Get the first day of the week in SQL Server

Question

I am trying to group records by week, keeping the aggregated date as the first day of the week. However, the standard technique that I use to round dates seems to work incorrectly with weeks (although it works for days, months, years, quarters, and any other terms to which I applied it).

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 Monday, not Sunday. Choosing @@datefirst returns 7 , which is the code for Sunday, so the server is configured correctly, as far as I know.

I can get around this fairly easily by changing the above code to:

 select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), -1); 

But the fact that I should make such an exception makes me a little complicated. Also, sorry if this is a duplicate question. I found some related questions, but no one addressed this aspect specifically.

Answer 1

To answer why you get Monday instead of Sunday:

You add a few weeks to date 0. What is date 0? 1900-01-01. What was the day 1900-01-01? Monday. So, in the code you say, how many weeks have passed since Monday, January 1, 1900? Let me call it [n]. Ok, now add [n] weeks on Monday, January 1, 1900. Do not be surprised that it ends on Monday. DATEADD has no idea that you want to add a few weeks, but only until you get to Sunday, just add 7 days, then add another 7 days ... just like DATEDIFF only recognizes borders that have been crossed. For example, they return 1, although some people complain that there must be some reasonable logic 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 Sunday:

If you want Sundays, select a base date, not Monday, but rather Sunday. For example:

 DECLARE @dt DATE = '1905-01-01'; SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt); 

This will not be broken if you change the DATEFIRST setting (or your code will be run for the user with a different setting) - provided that you still want Sunday, regardless of the current setting. If you want these two answers to be jive, then you should use a function that depends on the DATEFIRST parameter, for example.

 SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP); 

So, if you change your DATEFIRST parameter to Monday, Tuesday, what you have, the behavior will change. Depending on what behavior you want, you can use one of the following 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 

You now have many alternatives, but which one works best? I would be surprised if there were any significant differences, but I collected all the answers provided so far and conducted them through two sets of tests - one cheap and one expensive. I measured client statistics because I don’t see I / O or memory play a role in performance here (although they may go into the game depending on how the function is used). In my tests, the results are:

"Cheap" job request:

 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 

Dear assignment request:

 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, if desired, pass on the details of my tests - stopping here, as this is already quite long. I was a little surprised to see that Kurt came out as the fastest at the top end, given the amount of computation and the built-in code. Perhaps I will conduct more detailed tests and a blog about this ... if you have no objection to posting your functions elsewhere.

Answer 2

For them to be obtained:

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

There was a similar example above, but thanks to the double "% 7" it would be much slower.

Answer 3

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

Answer 4

I ran 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 >= @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

Answer 5

For those who need an answer at work, and the creation function is prohibited by your database administrator, the following solution will work:

 select *, cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-1), YourDate) as DATE) as WeekStart From..... 

This gives rise to this week. Here I assume that Sundays are the beginning of the week. If you think Monday is the beginning, you should use:

 select *, cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-2), YourDate) as DATE) as WeekStart From..... 

Answer 6

Perhaps 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 

Answer 7

 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

Answer 8

Since Julian date 0 is Monday, just add the number of weeks on Sunday which is the day before -1. select dateadd (wk, dateiff (wk, 0, getdate ()), - 1)

Answer 9

 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 week to Monday, and then calculate what the day of the week is when the day is given, and then use DateAdd and Case I to calculate what date was on the previous Monday of this week.

Answer 10

For basic (current week Sunday)

 select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) as date) 

If in the previous week:

 select cast(dateadd(day,-(datepart(dw,getdate())-1),getdate()) -7 as date) 

Internally, we created a function that does this, but if you need it quickly and dirty, it will do it.

Answer 11

I have no problems with any of the answers given here, however, I find mine much easier to implement and understand. I have not performed any performance tests, but this should be sloppy.

So, I got my answer from the fact that dates are stored on the SQL server as integers (I'm talking only about the date component). If you don't believe me, try this SELECT CONVERT (INT, GETDATE ()) and vice versa.

Now, knowing this, you can make cool math equations. Perhaps you can come up with the best, but here's 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, US 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)) 

Answer 12

I had a similar problem. Given the date, I wanted to get the date on Monday of that week.

I used the following logic: find the day number of the week in the range 0-6, and then subtract this from the start 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 method can be used for any starting day of the week.

Answer 13

I found this simple and useful. It works even if the first day of the 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)

Answer 14

Perhaps I am simplifying here, and it may be so, but it seems to work for me. You haven't had a problem with this 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'