SQL Server: Find Week Start And End DateTime

• Download source - 1.6 KB

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.

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)))

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.

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.

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)))

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

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;

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.

/*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.

/*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));

References

• Week start and end: https://stackoverflow.com/a/1267176/2948523
• @@DATEFIRST day numbers: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017
• @@DATEFIRST is local to session: https://stackoverflow.com/questions/883127/sql-server-set-datefirst-scope
• Find day name: https://database.guide/3-ways-to-get-the-day-name-from-a-date-in-sql-server-t-sql/
• Normalizing the first day of a week: https://www.itprotoday.com/sql-server/normalizing-first-day-week

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

• 10^th July, 2019: Initial version