Home Dashboard Directory Help
Search

Datediff function and the first day of the week by Pawel Potasinski, MSFT


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 239958
Opened: 11/21/2006 12:59:45 PM
Access Restriction: Public
1
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft on 10/18/2007 at 7:57 AM
Hello,

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.

Cheers,
-Isaac
Sign in to post a workaround.
Posted by Itzik Ben-Gan on 12/27/2009 at 6:48 AM
Subtract @@DATEFIRST days from both dates liek so:

SELECT DATEDIFF(week,
DATEADD(day, -@@DATEFIRST, '20091220'),
DATEADD(day, -@@DATEFIRST, '20091221'));