How does DATEDIFF calculate weekly differences in SQL Server 2005?

Question

I would like to calculate the difference in weeks between two dates when two dates are considered part of the same week if their previous Sunday is the same. Ideally, I would like to do this using DATEDIFF, instead of learning the complex idiom for calculating a value. But I can’t say how it works when weeks are involved.

The following query returns 1 and 2. This may make sense if your calendar week starts on Sunday, i.e. if you run SET DATEFIRST 7in advance or if @@DATEFIRSTby 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
;

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

? , ?

Answer 1

DATEDIFF DATEFIRST.

:

DATEDIFF T-SQL?