Get first day of week in SQL Server

Question

I am trying to group records by week  storing the aggregated date as the first day of the week  However  the standard technique I use for rounding off dates does not appear to work correctly with weeks  though it does for days  months  years  quarters and any other timeframe I ve applied it to    Here is the SQL   select  start of week    dateadd week  datediff week  0  getdate     0     This returns 2011-08-22 00 00 00 000  which is a Monday  not a Sunday  Selecting   datefirst returns 7  which is the code for Sunday  so the server is setup correctly in as far as I know   I can bypass this easily enough by changing the above code to   select  start of week    dateadd week  datediff week  0  getdate     -1     But the fact that I have to make such an exception makes me a little uneasy  Also  apologies if this is a duplicate question  I found some related questions but none that addressed this aspect specifically

User · Answer

Maybe I m over simplifying here  and that may be the case  but this seems to work for me  Haven t ran into any problems with it yet     CAST  1 1     CAST YEAR GETDATE    AS VARCHAR 30   AS DATETIME     DATEPART wk  YOUR DATE    7 - 7  as  FirstDayOfWeek  CAST  1 1     CAST YEAR GETDATE    AS VARCHAR 30   AS DATETIME     DATEPART wk  YOUR DATE    7  as  LastDayOfWeek

User · Answer

Googled this script   create function dbo F START OF WEEK        DATE           datetime      -- Sun   1  Mon   2  Tue   3  Wed   4     -- Thu   5  Fri   6  Sat   7     -- Default to Sunday      WEEK START DAY     int   1       Find the fisrt date on or before  DATE that matches  day of week of  WEEK START DAY     returns     datetime as begin declare   START OF WEEK DATE    datetime declare   FIRST BOW     datetime  -- Check for valid day of week if  WEEK START DAY between 1 and 7     begin     -- Find first day on or after 1753 1 1  -53690      -- matching day of week of  WEEK START DAY     -- 1753 1 1 is earliest possible SQL Server date      select  FIRST BOW   convert datetime -53690    WEEK START DAY 5  7       -- Verify beginning of week not before 1753 1 1     if  DATE  gt    FIRST BOW         begin         select  START OF WEEK DATE            dateadd dd  datediff dd  FIRST BOW  DATE  7  7  FIRST BOW          end     end  return  START OF WEEK DATE  end go   http   www sqlteam com forums topic asp TOPIC ID 47307

User · Answer

I had a similar problem   Given a date  I wanted to get the date of the Monday of that week   I used the following logic   Find the day number in the week in the range of 0-6  then subtract that from the originay date   I used  DATEADD day - DATEPART weekday   5  7    Since DATEPRRT weekday   returns 1   Sundaye     7 Saturday  DATEPART weekday   5  7 returns 0 Monday     6 Sunday   Subtracting this number of days from the original date gives the previous Monday   The same technique could be used for any starting day of the week

User · Answer

To answer why you re getting a Monday and not a Sunday   You re adding a number of weeks to the date 0  What is date 0  1900-01-01  What was the day on 1900-01-01  Monday  So in your code you re saying  how many weeks have passed since Monday  January 1  1900  Let s call that  n   Ok  now add  n  weeks to Monday  January 1  1900  You should not be surprised that this ends up being a Monday  DATEADD has no idea that you want to add weeks but only until you get to a Sunday  it s just adding 7 days  then adding 7 more days      just like DATEDIFF only recognizes boundaries that have been crossed  For example  these both return 1  even though some folks complain that there should be some sensible logic built in to round up or down   SELECT DATEDIFF YEAR   2010-01-01    2011-12-31    SELECT DATEDIFF YEAR   2010-12-31    2011-01-01      To answer how to get a Sunday   If you want a Sunday  then pick a base date that s not a Monday but rather a Sunday  For example   DECLARE  dt DATE    1905-01-01   SELECT  start of week    DATEADD WEEK  DATEDIFF WEEK   dt  CURRENT TIMESTAMP    dt     This will not break if you change your DATEFIRST setting  or your code is running for a user with a different setting  - provided that you still want a Sunday regardless of the current setting  If you want those two answers to jive  then you should use a function that does depend on the DATEFIRST setting  e g   SELECT DATEADD DAY  1-DATEPART WEEKDAY  CURRENT TIMESTAMP   CURRENT TIMESTAMP     So if you change your DATEFIRST setting to Monday  Tuesday  what have you  the behavior will change  Depending on which behavior you want  you could use one of these functions   CREATE FUNCTION dbo StartOfWeek1 -- always a Sunday        d DATE   RETURNS DATE AS BEGIN     RETURN  SELECT DATEADD WEEK  DATEDIFF WEEK   19050101    d    19050101     END GO      or     CREATE FUNCTION dbo StartOfWeek2 -- always the DATEFIRST weekday        d DATE   RETURNS DATE AS BEGIN     RETURN  SELECT DATEADD DAY  1-DATEPART WEEKDAY   d    d    END GO   Now  you have plenty of alternatives  but which one performs best  I d be surprised if there would be any major differences but I collected all the answers provided so far and ran them through two sets of tests - one cheap and one expensive  I measured client statistics because I don t see I O or memory playing a part in the performance here  though those may come into play depending on how the function is used   In my tests the results are    Cheap  assignment query   Function - client processing time   wait time on server replies   total exec time Gandarez     - 330 2029 2359 - 0 23 6 me datefirst - 329 2123 2452 - 0 24 5 me Sunday    - 357 2158 2515 - 0 25 2 trailmax     - 364 2160 2524 - 0 25 2 Curt         - 424 2202 2626 - 0 26 3    Expensive  assignment query   Function - client processing time   wait time on server replies   total exec time Curt         - 1003 134158 135054 - 2 15 Gandarez     -  957 142919 143876 - 2 24 me Sunday    -  932 166817 165885 - 2 47 me datefirst -  939 171698 172637 - 2 53 trailmax     -  958 173174 174132 - 2 54   I can relay the details of my tests if desired - stopping here as this is already getting quite long-winded  I was a bit surprised to see Curt s come out as the fastest at the high end  given the number of calculations and inline code  Maybe I ll run some more thorough tests and blog about it    if you guys don t have any objections to me publishing your functions elsewhere

User · Answer

I found this simple and usefull  Works even if first day of week is Sunday or Monday   DECLARE  BaseDate AS Date  SET  BaseDate   GETDATE    DECLARE  FisrtDOW AS Date  SELECT  FirstDOW   DATEADD d DATEPART WEEKDAY  BaseDate   -1   1   BaseDate

User · Answer

I don t have any issues with any of the answers given here  however I do think mine is a lot simpler to implement  and understand  I have not run any performance tests on it  but it should be neglegable   So I derived my answer from the fact that dates are stored in SQL server as integers   I am talking about the date component only   If you don t believe me  try this SELECT CONVERT INT  GETDATE     and vice versa   Now knowing this  you can do some cool math equations  You might be able to come up with a better one  but here is mine      TAKEN FROM http   msdn microsoft com en-us library ms181598 aspx First day of the week is 1 -- Monday 2 -- Tuesday 3 -- Wednesday 4 -- Thursday 5 -- Friday 6 -- Saturday 7  default  U S  English  -- Sunday     --Offset is required to compensate for the fact that my   DATEFIRST setting is 7  the default   DECLARE  offSet int   testDate datetime SELECT  offSet   1   testDate   GETDATE    SELECT CONVERT DATETIME  CONVERT INT   testDate  -  DATEPART WEEKDAY   testDate  -  offSet

User · Answer

For those who need the answer at work and creating function is forbidden by your DBA  the following solution will work   select    cast DATEADD day  -1  DATEPART WEEKDAY  YouDate -1   YourDate  as DATE  as WeekStart From        This gives the start of that week  Here I assume that Sundays are the start of weeks  If you think that Monday is the start  you should use    select    cast DATEADD day  -1  DATEPART WEEKDAY  YouDate -2   YourDate  as DATE  as WeekStart From

User · Answer

CREATE FUNCTION dbo fnFirstWorkingDayOfTheWeek        currentDate date   RETURNS INT AS BEGIN     -- get DATEFIRST setting     DECLARE  ds int     DATEFIRST      -- get week day number under current DATEFIRST setting     DECLARE  dow int   DATEPART dw  currentDate        DECLARE  wd  int    1     dow  ds    7  5    7  -- this is always return Mon as 1 Tue as 2     Sun as 7       RETURN DATEADD dd 1- wd  currentDate    END

User · Answer

For these that need to get    Monday   1 and Sunday   7   SELECT 1     5   DATEPART dw  GETDATE        DATEFIRST    7     Sunday   1 and Saturday   7   SELECT 1     6   DATEPART dw  GETDATE        DATEFIRST    7     Above there was a similar example  but thanks to double   7  it would be much slower

User · Answer

This works wonderfully for me    CREATE FUNCTION  dbo   StartOfWeek       INPUTDATE DATETIME   RETURNS DATETIME  AS BEGIN   -- THIS does not work in function    -- SET DATEFIRST 1 -- set monday to be the first day of week     DECLARE  DOW INT -- to store day of week   SET  INPUTDATE   CONVERT VARCHAR 10    INPUTDATE  111    SET  DOW   DATEPART DW   INPUTDATE     -- Magic convertion of monday to 1  tuesday to 2  etc    -- irrespect what SQL server thinks about start of the week    -- But here we have sunday marked as 0  but we fix this later    SET  DOW     DOW     DATEFIRST - 1   7   IF  DOW   0 SET  DOW   7 -- fix for sunday    RETURN DATEADD DD  1 -  DOW  INPUTDATE   END

User · Answer

Maybe you need this   SELECT DATEADD DD  1 - DATEPART DW  GETDATE     GETDATE      Or  DECLARE  MYDATE DATETIME SET  MYDATE    2011-08-23  SELECT DATEADD DD  1 - DATEPART DW   MYDATE    MYDATE    Function  CREATE FUNCTION  dbo   GetFirstDayOfWeek     pInputDate    DATETIME   RETURNS DATETIME BEGIN  SET  pInputDate   CONVERT VARCHAR 10    pInputDate  111  RETURN DATEADD DD  1 - DATEPART DW   pInputDate                   pInputDate   END GO

User · Answer

Since Julian date 0 is a Monday just add the number of weeks to Sunday which is the day before -1 Eg  select dateadd wk datediff wk 0 getdate    -1

User · Answer

For the basic  the current week s Sunday   select cast dateadd day - datepart dw getdate   -1  getdate    as date    If previous week   select cast dateadd day - datepart dw getdate   -1  getdate    -7 as date    Internally  we built a function that does it but if you need quick and dirty  this will do it

User · Answer

Set DateFirst 1   Select      Datepart wk  TimeByDay   Week       Dateadd d                  CASE                  WHEN  Datepart dw  TimeByDay    1 then 0                 WHEN  Datepart dw  TimeByDay    2 then -1                 WHEN  Datepart dw  TimeByDay    3 then -2                 WHEN  Datepart dw  TimeByDay    4 then -3                 WHEN  Datepart dw  TimeByDay    5 then -4                 WHEN  Datepart dw  TimeByDay    6 then -5                 WHEN  Datepart dw  TimeByDay    7 then -6                 END                   TimeByDay  as StartOfWeek  from TimeByDay Tbl   This is my logic  Set the first of the week to be Monday then calculate what is the day of the week a give day is  then using DateAdd and Case I calculate what the date would have been on the previous Monday of that week

[sql-server] Get first day of week in SQL Server

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to date