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:
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?