Get first day of week in SQL Server

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.

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.

---

If you're lucky enough to be on SQL Server 2022 or better (or Azure SQL Database / MI), you can use two new functions:

• DATETRUNC
• DATE_BUCKET

They are similarly affected by @@DATEFIRST settings, so you have to work around those. Actually DATETRUNC doesn't really offer any benefit unless you know you will always be in SET DATEFIRST 7 - you can make it work like the other workarounds here, but it's not any less complicated to do so. So, let's focus on DATE_BUCKET(). For February 2025, we want the first day of the week to be February 2nd, February 9th, and so on.

DECLARE @d table(d date);

INSERT @d SELECT d = dateadd(DAY, value, '20250201')
  FROM GENERATE_SERIES(0, 10);

-- I want Sunday as week boundary!
-- with default datefirst for 'merika: 7
  
  SELECT
    d,
    wd  = DATEPART(WEEKDAY, d),
    wdn = DATENAME(WEEKDAY, d),
    datebuck_wrong = DATE_BUCKET(WEEK, 1, d), -- wrong! based on Jan 1, 1900
    datetrnc_right = DATETRUNC(WEEK,d), -- correct
    oldapproach = DATEADD(DAY, -(DATEPART(WEEKDAY, d) + @@DATEFIRST + 6)%7, d)
  FROM @d;

-- even if someone made it Monday (or any other day!)

SET DATEFIRST 1;

  SELECT
    d,
    wd  = DATEPART(WEEKDAY, d),
    wdn = DATENAME(WEEKDAY, d),
    datebuck_wrong = DATE_BUCKET(WEEK, 1, d), -- still wrong
    datetrnc_wrong = DATETRUNC(WEEK,d), -- wrong!
    oldapproach = DATEADD(DAY, -(DATEPART(WEEKDAY, d) + @@DATEFIRST + 6)%7, d)
  FROM @d;

• db<>fiddle

We can coerce DATE_BUCKET() to work as we want by simply providing its optional fourth parameter - origin - any known Sunday, like the trick we used above with 1905-01-01. December 1st, 2024 was a Sunday, so we can use that:

DECLARE @d table(d date);

DECLARE  @origin_date date = '20241201'; -- known Sunday

INSERT @d SELECT d = dateadd(DAY, value, '20250201')
  FROM GENERATE_SERIES(0, 10);

-- I want Sunday as week boundary!
-- with default datefirst for 'merika: 7

  SELECT
    d,
    wd  = DATEPART(WEEKDAY, d),
    wdn = DATENAME(WEEKDAY, d),
    datebuck_wrong = DATE_BUCKET(WEEK, 1, d), -- wrong! based on Jan 1, 1900
    /* added: */
    datebuck_origin = DATE_BUCKET(WEEK, 1, d, @origin_date),
    datetrnc_right = DATETRUNC(WEEK,d), -- correct
    oldapproach = DATEADD(DAY, -(DATEPART(WEEKDAY, d) + @@DATEFIRST + 6)%7, d)
  FROM @d;

-- even if someone made it Monday (or any other day!)
SET DATEFIRST 1;

  SELECT
    d,
    wd  = DATEPART(WEEKDAY, d),
    wdn = DATENAME(WEEKDAY, d),
    datebuck = DATE_BUCKET(WEEK, 1, d), -- still wrong
    /* added: */
    datebuck_origin = DATE_BUCKET(WEEK, 1, d, @origin_date),
    datetrnc_wrong = DATETRUNC(WEEK,d), -- wrong!
    datefrst = DATEADD(DAY, -(DATEPART(WEEKDAY, d) + @@DATEFIRST + 6)%7, d)
  FROM @d;

• db<>fiddle

You might say, "Why not just manually hard-code SET DATEFIRST?" Well, that can't be done inside functions, for example, and also you can't just override users' settings who may be relying on the existing setting for other behaviors.