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.

Answer 1

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.