SQL Server - Finding the first day of the week

Question

I've been looking around for a chunk of code to find the first day of the current week, and everywhere I look I see this:

DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0)

Every place says this is the code I'm looking for.

The problem with this piece of code is that if you run it for Sunday it chooses the following Monday.

---

If I run:

SELECT GetDate() , DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0)

Results for today (Tuesday):

2013-05-14 09:36:39.650................2013-05-13 00:00:00.000

This is correct, it chooses Monday the 13th.

---

If I run:

SELECT GetDate()-1 , DATEADD(WK, DATEDIFF(WK,0,GETDATE()-1),0)

Results for yesterday (Monday):

2013-05-13 09:38:57.950................2013-05-13 00:00:00.000

This is correct, it chooses Monday the 13th.

---

If I run:

SELECT GetDate()-2 , DATEADD(WK, DATEDIFF(WK,0,GETDATE()-2),0)

Results for the 12th (Sunday):

2013-05-12 09:40:14.817................2013-05-13 00:00:00.000

This is NOT correct, it chooses Monday the 13th when it should choose the previous Monday, the 6th.

Can anyone illuminate me as to what's going in here? I find it hard to believe that no one has pointed out that this doesn't work, so I'm wondering what I'm missing.

Answer 1

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.