DateDiff SQL Server Problem

Question

Could some one please explain this to me as I am a touch confused as to why this is happening? Basically what I would like to know is why there is a difference between Sundays date and every other day of this week in weeks from the year 0. If that makes sense!

I tried setting the date first but this had no effect. Surely Monday – Sunday of this week should all have the same difference in weeks from the year zero?

Set Datefirst 1
Select DateName(dw,0) --Monday
Select DateDiff(week, 0, '20091109')--Monday: Difference 5732
Select DateDiff(week, 0, '20091114')--Saturday: Difference 5732
Select DateDiff(week, 0, '20091115')--Sunday: Difference 5733

What makes this even more bizarre is if you take the same two dates and date diff them you get one week for the one and 6 days for the other. Am I missing something here?

Select DateDiff(dd,'20091109','20091115')--6 Days difference
Select DateDiff(ww,'20091109','20091115')--1 Week difference

I am using SQL Server 2005

Answer 1

Sunday is considered the first day of the week in a number of countries.

Answer 2

I think the problem you are facing is the boundary dates. Are they inclusive?

Select DateDiff(dd,'20091109','20091115')--6 Days difference
Select DateDiff(dd,'20091109','20091116')--7 Days difference

DateDiff - Returns the number of date and time boundaries crossed between two specified dates. (Books Online Definition)

Answer 3

All the datediff functions do is count the number of date boundarys between the two datetime arguments passed to it. So if a week is defined to start Sunday, then the week boundary is midnight Sunday Morning.

So, From 11:59 Saturday night to 00:01 am Sunday Morning, will be the same datediff(week, x, y) as from 00:01 Sunday to 11:59 PM Saturday Night - 13 days later.

 x ------------------------------x    ==> 1 week diff
| Su M T W T F S | Su M T W T F S |
|                |                |
                x-x                   ==> Also 1 week diff

Answer 4

If you Set Datefirst = 1, Monday is the first day of the week, and the week calculations would use MOD 7 for number of days in the week and /7 for week count, in which sunday would be used as 1, and the others FLOORed to 0.