SQL DATEDIFF coercion differences between databases on same SQL instance?

Question

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?

Answer 1

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:

• What is the actual behavior of compatibility level 80?

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