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
