SQL Server Home
Datediff function and the first day of the week
Pawel Potasinski, MSFT
11/21/2006 12:59:45 PM
User(s) can reproduce this bug
It seems that DATEDIFF function always uses Sunday as the first day of the week. Even if you use SET DATEFIRST just before using DATEDIFF.
SQL Server 2005 SP1 - Developer Edition
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
SET DATEFIRST 1
SELECT @@DATEFIRST -- returns 1
SELECT DATEDIFF(ww,'2006-11-19','2006-11-21') -- returns 0
SELECT DATEDIFF(ww,'2006-11-19','2006-11-21') -- returns 1
to post a comment.
Please enter a comment.
on 10/18/2007 at 7:57 AM
It looks like this was closed without providing any feedback. The rationale for closing this was that changing the behavior to honor DATEFIRST would make it non-deterministic, and this would prevent building indexes on computed columns or views involving it. This behavior has been around for several versions, so this is a breaking change that doesn't seem worth it.
to post a workaround.
Please enter a workaround.
on 12/27/2009 at 6:48 AM
Subtract @@DATEFIRST days from both dates liek so:
DATEADD(day, -@@DATEFIRST, '20091220'),
DATEADD(day, -@@DATEFIRST, '20091221'));
© 2014 Microsoft