How does DATEDIFF calculate week differences in SQL Server 2005?

Posted by eksortso on Stack Overflow See other posts from Stack Overflow or by eksortso
Published on 2010-04-26T21:47:39Z Indexed on 2010/04/26 21:53 UTC
Read the original article Hit count: 225

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about datediff