Home Dashboard Directory Help
Search

The topic for datediff fails to mention that SET DATEFIRST does not apply by Erland Sommarskog


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 775886
Opened: 1/5/2013 12:43:53 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

It is not unreasonable to expect that datediff(week, date1, date2) would respect the setting of SET DATEFIRST. However this is not the case, because that would make the function nondeterministic as discussed in the response to item
https://connect.microsoft.com/SQLServer/feedback/details/239958/datediff-function-and-the-first-day-of-the-week#details

There is no reason to change this, but since users may expect a different behaviour, there is all reason to explicitly point out in the topics for datediff() and SET DATEFIRST that the DATEFIRST setting is not applicable to datediff.
Details
Sign in to post a comment.
Posted by Microsoft on 7/16/2013 at 5:22 PM
The DATEDIFF and SET DATEFIRST topics have been updated in SQL Server 2012 and 2014. The 2012 topic will be published the week of 07/23/2013. The 2014 topic will be published sometime in the Fall whenever CTP 2 is released.
Posted by Microsoft on 1/15/2013 at 12:09 PM
Thanks for the suggestion. Clarifying this behavior would indeed be useful.

Regards,
Gail Erickson
SQL Server Documentation Team
Sign in to post a workaround.