Click here to Skip to main content
14,240,044 members

SQL Server: Find Week Start And End DateTime

Rate this:
3.68 (7 votes)
Please Sign up or sign in to vote.
3.68 (7 votes)
10 Jul 2019CPOL
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.

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

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)

Share

About the Author

DiponRoy
Bangladesh Bangladesh
No Biography provided

Comments and Discussions

 
GeneralMy vote of 2 Pin
CurtisG12-Jul-19 4:39
professionalCurtisG12-Jul-19 4:39 
GeneralRe: My vote of 2 Pin
DiponRoy12-Jul-19 6:47
memberDiponRoy12-Jul-19 6:47 
GeneralRe: My vote of 2 Pin
Paw Jershauge19hrs 9mins ago
memberPaw Jershauge19hrs 9mins ago 
GeneralMy vote of 5 Pin
Hyland Computer Systems11-Jul-19 17:01
memberHyland Computer Systems11-Jul-19 17:01 
GeneralRe: My vote of 5 Pin
DiponRoy12-Jul-19 6:50
memberDiponRoy12-Jul-19 6:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 10 Jul 2019

Tagged as

Stats

3.4K views
54 downloads
10 bookmarked