How does DATEDIFF calculate week differences in SQL Server 2005?

Question

I would like to calculate the difference in weeks between two dates, where two dates are considered part of the same week if their preceding Sunday is the same. Ideally, I'd like to do this using DATEDIFF, instead of learning an elaborate idiom to calculate the value. But I can't tell how it works when weeks are involved.

The following query returns 1 and 2. This might make sense if your calendar week begins with a Sunday, i.e. if you run SET DATEFIRST 7 beforehand or if @@DATEFIRST is 7 by default.

SET DATEFIRST 7;
-- SET DATEFIRST 1;

DECLARE
    @d1 DATETIME,
    @d2a DATETIME,
    @d2b DATETIME
;
SELECT
    @d1 = '2010-04-05',   -- Monday
    @d2a = '2010-04-16',  -- Following Friday
    @d2b = '2010-04-18'   -- the Sunday following
;

SELECT
    DATEDIFF(week, @d1, @d2a) AS weekdiff_a   -- returns 1
    ,DATEDIFF(week, @d1, @d2b) AS weekdiff_b  -- returns 2
;

So I expected different results if SET DATEFIRST 1 is executed instead of SET DATEFIRST 7. But the return values are the same, regardless!

What is going on here? What should I do to get the correct week differences?

Answer 1

DATEDIFF doesn't respect the DATEFIRST setting.

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

http://stackoverflow.com/questions/1101892/is-it-possible-to-set-start-of-week-for-t-sql-datediff-function