SQL Server : group by week off by 1 day

Question

Using the syntax

dateadd(week, datediff(week, 0, CURRENT_TIMESTAMP),0)

to group data by week it appears that the last day of the week gets put into the following week.

In a simple example

print dateadd(week, datediff(week, 0, '27 jan 2012 00:00'),0)
print dateadd(week, datediff(week, 0, '28 jan 2012 00:00'),0)
print dateadd(week, datediff(week, 0, '29 jan 2012 00:00'),0)
print dateadd(week, datediff(week, 0, '30 jan 2012 00:00'),0)
print dateadd(week, datediff(week, 0, '31 jan 2012 00:00'),0)

I would expect to return

Jan 23 2012 12:00AM
Jan 23 2012 12:00AM
Jan 23 2012 12:00AM
Jan 30 2012 12:00AM
Jan 30 2012 12:00AM

or

Jan 22 2012 12:00AM
Jan 22 2012 12:00AM
Jan 29 2012 12:00AM
Jan 29 2012 12:00AM
Jan 29 2012 12:00AM

depending on DATEFIRST settings I guess

however it returns

Jan 23 2012 12:00AM
Jan 23 2012 12:00AM
Jan 30 2012 12:00AM
Jan 30 2012 12:00AM
Jan 30 2012 12:00AM

i.e. the 29 January is in the week of 30th January not 23rd January or 30th January as expected.

I'm clearly missing something but can't think what it might be.

Sure, the point is though that it appears to be inconsistent with itself — Justin Wignall, Feb 24 '12 at 13:23
I read up on 'set datefirst' and saw that you already knew about "first day of week", so I deleted my comment. — Hans Keﬆing, Feb 24 '12 at 13:26

score 4 · Accepted Answer · answered Feb 24 '12 at 13:26

This is correct.

No matter what @@DATEFIRST is, there is always the same number of whole weeks between now and 01 Jan 1900. As @@DATEFIRST changes, it affects both "start" and "end" week.

DATEADD then just adds 7 day periods because it ignores @@DATEFIRST

You can force it by choosing the DATEADD base

Example: 01 Jan 1900 is a Monday.

You want Sunday? Then base to 31 Dec 1899

print dateadd(week, datediff(week, 0, '27 jan 2012 00:00'),-1)
print dateadd(week, datediff(week, 0, '28 jan 2012 00:00'),-1)
print dateadd(week, datediff(week, 0, '29 jan 2012 00:00'),-1)
print dateadd(week, datediff(week, 0, '30 jan 2012 00:00'),-1)
print dateadd(week, datediff(week, 0, '31 jan 2012 00:00'),-1)

You want Tuesday? Then base to 02 Jan 1900

print dateadd(week, datediff(week, 0, '27 jan 2012 00:00'),1)
print dateadd(week, datediff(week, 0, '28 jan 2012 00:00'),1)
print dateadd(week, datediff(week, 0, '29 jan 2012 00:00'),1)
print dateadd(week, datediff(week, 0, '30 jan 2012 00:00'),1)
print dateadd(week, datediff(week, 0, '31 jan 2012 00:00'),1)

score 1 · Answer 2 · answered Feb 24 '12 at 13:18

1

This should convert the date to the first day of the week for the purposes of grouping

set datefirst 1
select dateadd(day, -1 * (datepart(weekday, getdate()) - 1), getdate())

Note that the set datefirst 1 sets the first day of the week as Monday. You can alter this statement to change the first day of the week to fit to your expectations (see http://msdn.microsoft.com/en-us/library/ms181598.aspx)

answered Feb 24 '12 at 13:18

Andrew Skirrow

3,402
18
41

I think that's incorrect. `DATEDIFF()` doens't see the `DATEFIRST`value. – aF. Feb 24 '12 at 13:27
Thanks Andy, guess I was hoping there was a 'catch all' syntax of grouping by day, week month etc. but I guess not. – Justin Wignall Feb 24 '12 at 13:29
@aF. That is why I'm not using DATEDIFF :-) – Andrew Skirrow Feb 24 '12 at 13:30
I think maybe aF has the 'reason' which is why Andy presented the correct answer. All sorted now, thanks again. – Justin Wignall Feb 24 '12 at 13:32
Ah, one point I missed was that I wanted to not only group by week but also return the date as the start of that week, @gbn's solution does that also – Justin Wignall Feb 24 '12 at 13:38
Indeed gbn's solution is better. – Andrew Skirrow Feb 24 '12 at 13:41

score 0 · Answer 3 · edited May 23 '17 at 09:59

0

DATEDIFF doesn't respect the DATEFIRST setting.

Here's a similar question, with a potential workaround:

Is it possible to set start of week for T-SQL DATEDIFF function?

edited May 23 '17 at 09:59

Community

1
1

answered Feb 24 '12 at 13:26

aF.

64,980
43
135
198

SQL Server : group by week off by 1 day

3 Answers3