2

When using the dateadd/datdiff trick to round dates I've always had very predictable and understandable results with days and months. I am now using it for the first time with weeks and am experiencing something I was not expecting.
The 19th and 26th of July 2015 seem to get moved into what I consider the next week. The following example illustrates the actual behavior in the second column and the expected behavior in the third column. I'm trying to understand WHY and HOW there is a difference.

declare @n as table ([N] [int] )

insert into @n ( [N] ) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
select n
,      dateadd(dd,n,'2015-jul-17')
,      dateadd(wk,datediff(wk,0,  dateadd(dd,n,'2015-jul-17')  ),0)
,      dateadd(dd,datediff(dd,0,  dateadd(dd,n,'2015-jul-17')  )/7*7,0)
from @n
order by n
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
feihtthief
  • 6,403
  • 6
  • 30
  • 29

2 Answers2

3

The problem is DATEDIFF() by week using Sunday as the start day of the week instead of Monday.

A trick is -1:

declare @n as table ([N] [int])
declare @start datetime = '1900-01-01'


insert into @n ( [N] ) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)
select n
,                           dateadd(dd,n,@start) as [Date]
,dateadd(wk,datediff(wk,0,  dateadd(dd,n,@start) -1 ),0) as [WeekStart (by WeekDiff)]
,dateadd(dd,datediff(dd,0,  dateadd(dd,n,@start)    )/7*7,0) as [WeekStart (by DayDiff)]
,           datediff(wk,0,  dateadd(dd,n,@start)) [WeekDiff]
from @n
order by n

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

Edit

Using DATEPART(WEEKDAY) with SET DATEFIRST 1

SET DATEFIRST 1

;with dates(value) as (select convert(date, dateadd(dd,n,@start)) from @n)
select *, DATEADD(Day, -DATEPART(WEEKDAY, value) + 1, value) from dates
Community
  • 1
  • 1
Eric
  • 5,675
  • 16
  • 24
  • Thanks. I had never noticed that datediff had a fixed day of the week for the start of the week. The -1 trick is cool. Now I just need to decide which implementation will make more sense 3 months from now when I need to maintain this code :| – feihtthief Aug 03 '15 at 09:57
0

This date rounding algorithm, translated to English, could be worded as follows:

Add a number of [Time Unit] to the start of calendar (Day 0)

where [Time Unit] is Year/Month/Days/Hours etc..... And Start of calendar is 1900/01/01 00:00:00 in T-SQL.

1900/01/01 is a Monday.

So you are adding the number of full weeks to a Monday. And hence the week is always rounded to Monday. This throws 7/19 and 7/26 (Sundays) to the end of the week.

Alan
  • 324
  • 1
  • 6
  • Not true, if you change the start of week using `SET DATEFIRST {#}` it doesn't change, also the 19th is a Sunday, should have set it to the previous Monday (not the day after) – Andrew Bickerton Aug 03 '15 at 09:50
  • I am not talking about start of week, I am talking about day 0 is a Monday. OP is getting the number of weeks from Day 0, and then add it back to Day 0 for get the start of the weeks. But adding weeks to Day 0 means adding to Monday because Day 0 is a Monday. Whether DATEDIFF use Sunday or Monday it doesn't care, the result won't be different. (And of course I know that DATEDIFF must use Sunday and it cannot be changed) – Alan Aug 03 '15 at 10:01
  • If you want to know what I am saying, try replacing the two 0 by '1970-1-1' in the line of script for 'wk'. You will get weeks started on Thursday! This is why the -1 trick is working. It is not because of start of week. It is because of Day 0. – Alan Aug 03 '15 at 10:13