CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Ron Steinhauser
Ron Steinhauser

Reputation: 51

SQL Server - Finding the first day of the week

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.

Upvotes: 5

Views: 9235

Answers (4)

AlvaroCachoperro
AlvaroCachoperro

Reputation: 740

That worked for me like a charm:

Setting moday as first day of the week without changing DATEFIRST variable:

-- FirstDayWeek    
select dateadd(dd,(datepart(dw, case datepart(dw, [yourDate]) when 1 then dateadd(dd,-1,[yourDate]) else [yourDate] end) * -1) + 2, case datepart(dw, [yourDate]) when 1 then dateadd(dd,-1,[yourDate]) else [yourDate] end) as FirstDayWeek;

-- LastDayWeek
select dateadd(dd, (case datepart(dw, [yourDate]) when 1 then datepart(dw, dateadd(dd,-1,[yourDate])) else datepart(dw, [yourDate]) end * -1) + 8, case datepart(dw, [yourDate]) when 1 then dateadd(dd,-1,[yourDate]) else [yourDate] end) as LastDayWeek;

Setting sunday as fist day of the week without changing DATEFIRST variable

select convert(varchar(50), dateadd(dd, (datepart(dw, [yourDate]) * -1) + 2, [yourDate]), 103) as FirstDayWeek, convert(varchar(50), dateadd(dd, (datepart(dw, [yourDate]) * -1) + 8, [yourDate]), 103) as LastDayWeek;

You can change [yourDate] by GETDATE() for testing

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

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.

Upvotes: 6

Jasmina Shevchenko
Jasmina Shevchenko

Reputation: 797

Try this one -

SET DATEFIRST 1

DECLARE @Date DATETIME
SELECT @Date = GETDATE()

SELECT CAST(DATEADD(DAY, 1 - DATEPART(WEEKDAY, @Date), @Date) AS DATE)

SELECT CAST(@Date - 2 AS DATE), CAST(DATEADD(WK, DATEDIFF(WK, 0, @Date-2), 0) AS DATE)

Results:

---------- ----------
2013-05-12 2013-05-13

Upvotes: 4

ExactaBox
ExactaBox

Reputation: 3385

SQL Server has a SET DATEFIRST function which allows you to tell it what the first day of the week should be. SET DATEFIRST = 1 tells it to consider Monday as the first day of the week. You should check what the server's default setting is via @@DATEFIRST. Or you could simply change it at the start of your query.

Some references:

MSDN

Similar Question

Upvotes: 2

Related Questions