locked
Week Start Date and End Date RRS feed

  • Question

  • Hi,

    If week start date is Sunday, the below query will use to get week start date and week end date.

    SELECT GETDATE()
    SELECT DATEADD(DAY,-DATEPART(WEEKDAY,GETDATE())+1, GETDATE())
    SELECT DATEADD(DAY,-DATEPART(WEEKDAY,GETDATE())+7, GETDATE())

    I didn't get any direct query for if week starts from Monday. Can you please any one share query for week start date and week end date ?

    Thanks in advance. 


    Srinivas Ch

    Monday, May 4, 2020 10:02 AM

Answers

All replies

  • The result of your query depends on your regional/language setting and here especially of DATEFIRST setting ; where 1 = Monday is:

    SET DATEFIRST 1;
    
    SELECT GETDATE()
    SELECT DATEADD(DAY,-DATEPART(WEEKDAY,GETDATE())+1, GETDATE())
    SELECT DATEADD(DAY,-DATEPART(WEEKDAY,GETDATE())+7, GETDATE()) 



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by srinivas_ch Monday, May 4, 2020 11:17 AM
    Monday, May 4, 2020 10:16 AM
  • select GETDATE() - datepart(weekday, GETDATE() + @@datefirst - 1) + 1 as week_start_monday

    The @@DATEFIRST in SQL Server is one of the Date and Time Function, which will return the first day of the week. This value is between 1 and 7. If your default language is US English, then by default, 7 is returned.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence





    Monday, May 4, 2020 10:18 AM
    Answerer
  • Got it. Thanks you

    Srinivas Ch

    Monday, May 4, 2020 11:17 AM
  • Thank you.

    Srinivas Ch

    Monday, May 4, 2020 11:18 AM
  • select GETDATE() - datepart(weekday, GETDATE() + @@datefirst - 1) + 1 as week_start_monday

    The @@DATEFIRST in SQL Server is one of the Date and Time Function, which will return the first day of the week. This value is between 1 and 7. If your default language is US English, then by default, 7 is returned.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence 

    Just a note: This will not work well on DATE or DATETIME2 but does work on DATETIME (Microsoft recommend to use DATETIME2 instead of DATETIME)

    declare @D DateTIME = GETDATE()
    select @D - datepart(weekday, @D + @@datefirst - 1) + 1 as week_start_monday
    GO -- OK
    declare @D Date = GETDATE()
    select @D - datepart(weekday, @D + @@datefirst - 1) + 1 as week_start_monday
    GO -- ERROR
    declare @D DateTIME2 = GETDATE()
    select @D - datepart(weekday, @D + @@datefirst - 1) + 1 as week_start_monday
    GO -- ERROR 

    I HIGHLY recommend NOT to use explicit CONVERT.

    The following solution will work on DATE, DATETIME, or DATETIME2

    declare @D Date = GETDATE()
    select DATEADD(DAY, -datepart(weekday, DATEADD(DAY,@@datefirst - 1,@D)) + 1,@D) as week_start_monday
    GO -- ERROR
    declare @D DateTIME = GETDATE()
    select DATEADD(DAY, -datepart(weekday, DATEADD(DAY,@@datefirst - 1,@D)) + 1,@D) as week_start_monday
    GO -- ERROR
    declare @D DateTIME2 = GETDATE()
    select DATEADD(DAY, -datepart(weekday, DATEADD(DAY,@@datefirst - 1,@D)) + 1,@D) as week_start_monday
    GO -- ERROR

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Monday, May 4, 2020 12:25 PM
    Monday, May 4, 2020 12:15 PM
  • You can calculate this value without relying on DATEFIRST.

    Declare @currentDate datetime = getdate();
    
     Select @currentDate
          , dateadd(day, -datediff(day, 0, @currentDate) % 7, @currentDate)
          , dateadd(day, -datediff(day, 0, @currentDate) % 7 + 6, @currentDate)
    

    If you want Sunday to be the start:

    Declare @currentDate datetime = getdate();
    
     Select @currentDate
          , dateadd(day, -datediff(day, -1, @currentDate) % 7, @currentDate)
          , dateadd(day, -datediff(day, -1, @currentDate) % 7 + 6, @currentDate)
    

    And if you want to remove the time portion - make sure @currentDate does not include the time using either:

    Declare @currentDate datetime = cast(getdate() As date);
    Declare @currentDate dateimte = dateadd(day, datediff(day, 0, getdate()), 0);

    Or - use a DATE data type instead...



    Jeff Williams

    • Proposed as answer by pituachMVP Monday, May 4, 2020 7:51 PM
    Monday, May 4, 2020 7:08 PM