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.

DECLARE @YEAR int = 2020;
DECLARE @WEEKSTOADD int = 6;
SET DATEFIRST 1;

SELECT 
        DATEADD(day, 
                1 -  DATEPART(dw,DATEADD(week,@WEEKSTOADD,cast(cast(@YEAR as varchar(4)) + '0101' as date))), 
                DATEADD(week,@WEEKSTOADD,cast(cast(@YEAR as varchar(4)) + '0101' as date)))

Here's how you can do it:

DECLARE @yourdate date = getdate()
Select dateadd(ww, datediff(ww, 0, @yourdate), 0)
Select dateadd(ww, datediff(ww, 0, @yourdate), 4)

You set @yourdate to the date you want. The first SELECT will give you the first day and the second SELECT will give you the last date

It is DATEDIFF that returns the "incorrect" difference of weeks, which in the end results in the wrong Monday. And that is because DATEDIFF(WEEK, ...) doesn't respect the DATEFIRST setting, which I'm assuming you have set to 1 (Monday), and instead always considers the week crossing to be from Saturday to Sunday, or, in other words, it unconditionally considers Sunday to be the first day of the week in this context.

As for an explanation for that, so far I haven't been able to find an official one, but I believe this must have something to do with the DATEDIFF function being one of those SQL Server treats as always deterministic. Apparently, if DATEDIFF(WEEK, ...) relied on the DATEFIRST, it could no longer be considered always deterministic, which I can only guess wasn't how the developers of SQL Server wanted it.

To find the first day of the week's date, I would (and most often do actually) use the first suggestion in @Jasmina Shevchenko's answer:

DATEADD(DAY, 1 - DATEPART(WEEKDAY, @Date), @Date)

DATEPART does respect the DATEFIRST setting and (most likely as a result) it is absent from the list of always deterministic functions.

Use DATENAME or DATEPART:

SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6

These statements should do what you want in TSQL. Note, the statements are based on the current date. You can replace getdate() for whatever date you wish:

Select dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) as LastWeekStart
Select dateadd(wk, datediff(wk, 0, getdate()), 0) as ThisWeekStart
Select dateadd(wk, datediff(wk, 0, getdate()) + 1, 0) as NextWeekStart

There are lots of other date routines here.