Home Dashboard Directory Help

Confusing DATEDIFF behavior/documentation discrepancy by Steve Kass



Sign in
to vote
Type: Suggestion
ID: 290269
Opened: 8/1/2007 7:56:50 PM
Access Restriction: Public


Books Online says about DATEDIFF that "A time zone offset component of startdate or endate is not used in calculating the return value."

However, this is not how DATEDIFF works. When used with datetimeoffset values, DATEDIFF behaves in a confusing way. For example, the number of days between 7:00PM New York time and 11:00PM New York time on the same day (in New York) is given as 1.

Apparently when given datetimeoffset values, day boundaries means UTC day boundaries. This should be documented, if it is the desired behavior.

If this is not the desired behavior (and a Greenwich-centric function doesn't make sense to me), a decision must be made as to whether DATEDIFF considers day boundaries in the timezone of the first date-and-time argument or in the timezone of the second date-and-time argument.
Sign in to post a comment.
Posted by Microsoft on 1/18/2013 at 4:44 PM
Hello Steve,
We won't be able to address this request anytime soon. So I am closing this request.

Umachandar, SQL Programmability Team
Posted by CountryStyle on 12/18/2012 at 5:52 PM
The documentation for datediff() still does not reflect this annoying behavior of DATEDIFF() converting the SYSDATETIMEOFFSET value to UTC for its calculations.

The way datediff functions goes against what SYSDATETIMEOFFSET was designed to be simply a marker for the values timezone.

I agree with the others that another parameter (or dateDiffUTC()??)is required. The current method is just plain broken. Sometimes I just want to know what what orders where placed by midnight for each timezone. I and my customers could care less if an order was placed at midnight UTC.

One big reason for me using sysdatetimeoffset was for convenience. WCF doesn't support it and neither does datediff(). It is making me regret my decision to use it and it will cost me many days of work to replace it with 2 columns.

Microsoft please fix this broken implementation.
Posted by Alejandro Leguizamo on 8/7/2007 at 5:45 AM
I like the two step approach better. It is more reliable and allows the developer to have a defined default behavior, and override that behavior if required.
Posted by TiborK on 8/7/2007 at 2:26 AM
I would go for a two-step approach:

1. Exception if the two don't have the same time zone (basically a data type conversion error). I always prefer exception instead of logical errors. This way, the developer will pretty immediate see the issue at hand and can take apropriate measures.

2. An optional parameter to DATEDIFF, allowing the developer to specify 1 = first param, 2 = second param or 3 = UTC.

Above gives highest protection against logical errors but also allow flexibility, but only *after* a conscious desicion was made by the developer.
Posted by Microsoft on 8/6/2007 at 5:34 PM
Thanks for the question and great feedback.

The current design for DATEDIFF(and/or DATEADD) on argument(s) of DATETIMEOFFSET (SQL Server 2008) type is based on UTC rather than the local datetime value of the specified time zone for the given value. So what you have seen is an expected behavior.

The main reason of the design is not only because DATETIMEOFFSET is an UTC oriented type, and also because we want to avoid the ambiguous of datetimes across regions.

We'll make sure the documentation is well specified to reflect it in more details.

I understand your point on special DATETIMEOFFSET values that are given in the same time zone. Would the alternative of casting to date/datetime2/datetime (will convert to local datetime by default) before DATEDIFF work for you? Or would you prefer that we update the design to make DATEDIFF by doing the followings?
1. Based on local datetime for given values in the same time zone
2. Still based on UTC for given values in different time zone
Sign in to post a workaround.