Search code examples
sql-serverdatabasecoercion

SQL DATEDIFF coercion differences between databases on same SQL instance?

I have a data coercion mystery. I am seeing two different behaviors 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:

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

ISO 8601 date literal format citation: https://msdn.microsoft.com/en-us/library/ms187819.aspx

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

One of them returns zero rows, as you would expect.

Server one returns an error about the date range of the '1/1/0001':

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 '1/1/0001' and it fails as I expect for a default 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?

Edits:

  • Converted query to ISO date literals to avoid any ambiguity on formatting. Still seeing the same behavior.
  • Added DBCC USEROPTOINS check for dateformat, both mdy
Solution

  • https://dba.stackexchange.com/questions/96101/sql-datediff-coercion-differences-between-databases-on-same-sql-instance

    As answered by Aaron Bertrand on the DBA stack exchange site, this was rooted in compatibility levels.

    SELECT compatibility_level
FROM sys.databases WHERE name = 'FirstDatabase'

90

    VS

    SELECT compatibility_level
FROM sys.databases WHERE name = 'SecondDatabase'

110

    Aaron has an excellent write up of why in this question: https://dba.stackexchange.com/questions/44908/what-is-the-actual-behavior-of-compatibility-level-80

    See Conversions involving new date/time types

    The higher compatibility level likely means DateDiff uses a DateTime2 or other 'wider' data type and works. At 90 or below, it probably uses the old DateTime, and thus has conversion errors.

    Thanks to Tab Alleman for the suggestion to cross post.