CODEWITHSUNDEEP
sqlsql-serverdaterounding
feihtthief
feihtthief

Reputation: 7083

Why does SQL Server dateadd datediff behave behave this way when using weeks

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

Upvotes: 2

Views: 264

Answers (2)

Eric
Eric

Reputation: 5743

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

Upvotes: 3

Alan
Alan

Reputation: 334

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.

Upvotes: 0

Related Questions