2

I have a data coercion mystery. I am seeing two different behaviors from DATEDIFF for the same query, and I cannot understand why.

This is an extract of the relevant part of the query in question, with fixed values. The first value represents "today" in our query, and was set up with the same data type with an explicit CAST. The other value being passed in is from C# DateTime.MinValue. I am aware that it is below the valid SQL DATETIME range, but I am interested in the differences here:

-- dates in ISO 8601 literal format to avoid ambiguity
SELECT DATEDIFF(dd,CAST('2014-03-24' AS SmallDateTime),'0001-01-01')

We have two different databases on the same SQL server instance.

Server one returns an error about the date range of the '0001-01-01':

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Server two returns a value that looks ballpark correct:

-735315

The problematic date is almost certainly '0001-01-01' and it fails as I expect for a datetime, as it is below the minimum SQL datetime of Jan 1, 1753. (https://msdn.microsoft.com/en-us/library/ms187819.aspx)

According to the MSDN page for datediff (https://msdn.microsoft.com/en-US/library/ms189794(v=SQL.105).aspx), it can accept the following values:

 startdate is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset

Cast results for each of these are identical between servers, and are listed here:

SELECT CAST('0001-01-01' As time) -- works: 00:00:00.0000000
SELECT CAST('0001-01-01' As date) -- works: 0001-01-01
SELECT CAST('0001-01-01' As smalldatetime) -- error: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As DateTime) -- error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As datetime2) -- works: 0001-01-01 00:00:00.0000000
SELECT CAST('0001-01-01' As datetimeoffset) -- works: 0001-01-01 00:00:00.0000000 +00:00

The error explicitly calls out a failed DateTime conversion, so that appears to be the coercion of choice on database one.

It seems database two uses a different coercion that succeeds and does the datediff math correctly.

Because both of these databases are on the same SQL instance, I am ruling out instance settings.

Here are a few database settings we thought to check, and they appear identical between the two databases as well (checked in SQL Server Management Studio):

Database Collation (should be per server, but included for clarity):

(database) > Right Click > Properties > General > Maintenance > Collation
Database one: SQL_Latin1_General_CP1_CI_AS
Database two: SQL_Latin1_General_CP1_CI_AS

Date Correlation Optimization Enabled:

(database) > Right Click > Properties > Options > Misc. > Date Correlation Optimization Enabled
Database one: False
Database two: False

Two Digit Year Cutoff:

(database) > Right Click > Properties > Options > Containment > Two Digit Year Cutoff
Database one: 2049
Database two: 2049

User options date format

DBCC USEROPTIONS
Database one, dateformat: mdy
Database two, dateformat: mdy
(other settings appear identical)

I'm happy to provide other settings, or test query results, let me know what you'd like to see.

Why are the two databases behaving differently for this identical query? Why does it appear that the coercion chosen is different?

  • 1
    I bet one of your databases is in an older compatibility level. – Aaron Bertrand Mar 24 '15 at 17:30
  • 1
    (also, how much productivity did you gain by typing dd? Please use whole, unambiguous words like day. – Aaron Bertrand Mar 24 '15 at 17:31
  • Compatibility levels ARE different, nice catch. I'm testing and writing this up now. Thanks for the dd/day tip. I didn't write the code this was extracted from, but it's good to know about for future. – Colin Dabritz Mar 24 '15 at 17:35
3

This will happen if you are in a lower compatibility level (80) and indirectly try to convert a value that is outside the supported range of the older types. I have added an example illustrating exactly this scenario in the following answer:

See the section of my answer entitled Conversions involving new date/time types.

  • This was the answer. So in this case datediff on Compatibility mode (SQL 2005) 90 or earlier fails, as it tries to do a datediff conversion with a datetime, and the date is out the valid range. Testing on (SQL 2008) 100 or later works, probably doing a better (working) conversion internally like datetime2. Thanks so much! – Colin Dabritz Mar 24 '15 at 17:45
  • This was originally posted in on stackoverflow here: stackoverflow.com/questions/29238048/… – Colin Dabritz Mar 24 '15 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.