4

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.

gaige
  • 17,263
  • 6
  • 57
  • 68
Ron Steinhauser
  • 41
  • 1
  • 2
  • I believe the root of the issue is the use of the date 0 which in SQL Server resolves to '1900-01-01' - this was a Monday. – Oliver Gray May 14 '13 at 17:14
  • 1
    possible duplicate of [Get first day of week in SQL Server](http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server) – Pondlife May 14 '13 at 19:03
  • 1
    @Pondlife: Not really. The issue is why different Mondays are returned depending if the date is a Sunday or any other day of week. – Andriy M May 15 '13 at 11:14

4 Answers4

6

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.

Community
  • 1
  • 1
Andriy M
  • 76,112
  • 17
  • 94
  • 154
4

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
Jasmina Shevchenko
  • 797
  • 6
  • 17
2

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

Community
  • 1
  • 1
ExactaBox
  • 3,235
  • 16
  • 27
  • DATEDIFF(WEEK) disregards the DATEFIRST setting, and that is why the wrong Monday is returned for a Sunday date (when @@DATEFIRST = 1). – Andriy M May 15 '13 at 11:11
0

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

AlvaroCachoperro
  • 711
  • 6
  • 12