Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

SQL Server: Find Week Start And End DateTime

3.68/5 (7 votes)
10 Jul 2019CPOL2 min read 68.3K   102  
Finding week start and end date time from a given date time

Introduction

The aim of this article is to find the week start and end DATE/DATETIME for a given DATETIME object.

What are we going to do:

  1. Check the SQL Server default feature
  2. Explore other options and techniques
  3. Use a custom function

Week Start With Default Day

By default, SQL Server week start date is Sunday. Here, we are populating week DATE/DATETIME range for current DateTime.

SQL
DECLARE @dateTimeNow DATETIME = GETDATE();
SELECT 
    [StartDate] = DATEADD(dd, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                  DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0)),
    [EndDate] = DATEADD(dd, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
                DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0));
SELECT 
    [StartDateTime] = DATEADD(DAY, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                      DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0)),
    [EndDateTime] = DATEADD(DAY, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
                    DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0)))

Image 1

Set Week Start Day

In SQL Server, there is a @@DATEFIRST function, which returns the current week start day (value of SET DATEFIRST). To change default week start day, we can set any week start day value between 1-7 to DATEFIRST.

Image 2

SQL
SELECT @@DATEFIRST; 
SET DATEFIRST 7;     /*setting week start to 'Sunday'*/
  • @@DATEFIRST is local to the session. We can verify it by opening different tabs in SQL Server Management Studio and executing set/select code in the different tabs.
  • Change in DATEFIRST value has an impact at DATEPART(WEEKDAY, ).

1. Set 'DATEFIRST'

Here, we are setting week start day to Sunday.

SQL
SET DATEFIRST 7;     /*setting week start to 'Sunday'*/
DECLARE @dateTimeNow DATETIME = GETDATE();
SELECT 
    [StartDate] = DATEADD(dd, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                  DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0)),
    [EndDate] = DATEADD(dd, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
                DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0));
SELECT 
    [StartDateTime] = DATEADD(DAY, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                      DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0)),
    [EndDateTime] = DATEADD(DAY, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
               DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0)))

Image 3

2. Set 'DATEFIRST' And Restore To Default After Any Logical Operation

If we need to use multiple weeks start day value in the same query/session, we can do:

  1. Take a backup of the current @@DATEFIRST
  2. Set DATEFIRST with an expected week start day
  3. Do any logical operation
  4. Reset DATEFIRST from backup after the operation
SQL
DECLARE @dbDefaultWeekStart INTEGER = @@DATEFIRST; /*take backup of db default week start day*/    
DECLARE @expectedWeekStart INTEGER = 6;            /*expected week start from 'Saturday'*/    
SET DATEFIRST @expectedWeekStart;                  /*set week start day as expected*/

/*doing our calculation as needed*/
DECLARE @dateTimeNow DATETIME = GETDATE();
SELECT 
    [StartDate] = DATEADD(dd, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                  DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0)),
    [EndDate] = DATEADD(dd, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
                DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0));
SELECT 
    [StartDateTime] = DATEADD(DAY, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                      DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0)),
    [EndDateTime] = DATEADD(DAY, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
         DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0)))

/*reset week start date to its default or as it was*/
SET DATEFIRST @dbDefaultWeekStart;

Image 4

Custom Helper Function

Week Function

Here, we are going to create a week helper function...

Which will take inputs:

  • @weekStartDay INTEGER - is required and should be any between 1-7
  • @dateTime DATETIME - is required
  • @weekPart VARCHAR(10)-is required and should be any among ('Start', 'Middle', 'End')

and will output:

  • Expected week part ('Start', 'Middle', 'End') as DateTime object.
SQL
/*create function*/
IF OBJECT_ID(N'WeekPart', N'FN') IS NOT NULL
    DROP FUNCTION WeekPart;
GO
CREATE FUNCTION WeekPart(@weekStartDay INTEGER, @dateTime DATETIME, @weekPart VARCHAR(10))
RETURNS DATETIME
AS 
BEGIN    
    /*validations*/
    IF @dateTime IS NULL
    BEGIN
        RETURN @dateTime;
    END
    IF @weekStartDay NOT BETWEEN 1 AND 7
    BEGIN
        RETURN CAST('week start day value should be BETWEEN 1 AND 7' AS INT);
    END
    IF @weekPart NOT IN('Start', 'Middle', 'End')
    BEGIN
        RETURN CAST('week part should be IN(Start, Middle, End)' AS INT);
    END

    /*date to day number: https://docs.microsoft.com/en-us/sql/t-sql/statements/
      set-datefirst-transact-sql?view=sql-server-2017*/
    DECLARE @dayNumber INTEGER;
    SELECT @dayNumber =
        CASE DATENAME(WEEKDAY, @dateTime)
            WHEN 'Monday'    THEN 1
            WHEN 'Tuesday'    THEN 2
            WHEN 'Wednesday'THEN 3
            WHEN 'Thursday'    THEN 4
            WHEN 'Friday'    THEN 5
            WHEN 'Saturday'    THEN 6
            WHEN 'Sunday'    THEN 7
        END;

    /*calculate result*/
    DECLARE @difference INTEGER = -((7+@dayNumber-@weekStartDay)%7);
    DECLARE @startDateTime DATETIME = DATEADD(dd, @difference, @dateTime);
    DECLARE @resultDateTime DATETIME;
    SELECT @resultDateTime = 
        CASE @weekPart
            WHEN 'Start'    THEN @startDateTime
            WHEN 'Middle'    THEN DATEADD(dd, 3, @startDateTime)
            WHEN 'End'        THEN DATEADD(dd, 6, @startDateTime)
            ELSE @dateTime
        END;

    RETURN @resultDateTime;
END;

Using the Function

Let's use the created function in the query, where we are setting Saturday as the week start day. If needed, we can even pass SQL Servers @@DATEFIRST value as a parameter.

SQL
/*result*/
DECLARE @dateTimeNow DATETIME = GETDATE();
DECLARE @expectedWeekStart INTEGER;
SET @expectedWeekStart = 6;                   /*set 'Saturday', without changing db default*/
--SELECT @expectedWeekStart = @@DATEFIRST;    /*using db default*/

SELECT 
    [NowDate] = CAST(@dateTimeNow AS DATE),
    [WeekStartDate] = CAST(dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'start') AS DATE),
    [WeekMiddleDate] = CAST(dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'middle') AS DATE),
    [WeekEndDate] = CAST(dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'end') AS DATE);
SELECT 
    [NowDate] = DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0),
    [WeekStartDateTime] = DATEADD(dd, DATEDIFF(dd, 0, _
                  dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'start')), 0),
    [WeekMiddleDateTime] = DATEADD(dd, DATEDIFF(dd, 0, _
                  dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'middle')), 0),
    [WeekEndDate] = DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, _
                  dbo.WeekPart(@expectedWeekStart, @dateTimeNow, 'end')) + 1, 0));

Image 5

References

Source Code

Please find the SQL code file as an attachment.

Limitations

This is a learning purpose post. The code may throw unexpected errors for untested inputs. If any, just let me know.

History

  • 10th July, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)