Why doesn't SQL Server automatically convert DATE to DATETIME for comparison purposes?

Question

I like the new DATE data type in SQL Server 2008, but when I compare the DATE field with the DATETIME field on the linked server (in this case, SQL 2005), for example:

DECLARE @MyDate DATE SET @MyDate = CONVERT(DATE, GETDATE()) SELECT * FROM MySQL2005LinkedServer.SomeDB.dbo.SomeTable WHERE SomeDatetimeField < @MyDate 

I get this error:

 OLE DB provider "SQLNCLI10" returned message "Unspecified error". OLE DB provider "SQLNCLI10" returned message "The scale is invalid.". 

The "scale is invalid", obviously, because the Native client passes the DATatype back to the linked server, and since it is SQL 2005, it does not know what to do with it. Fulfilling the same query with the 2008 server works very well - SQL Server can easily compare DATE and DATETIME data types.

Here my question is - is there a reason why the root client does not automatically convert the DATE value '2009-11-09' to DATETIME from '2009-11-09 00: 00: 00.000' so that the previous version of SQL Server does not strangle it?

Answer 1

The internal structure for datetime (2005) and date / time / datetime2 datetimeoffset (2008) is very different from each other, and, as with other comparisons, the data must be mapped to the same type. Thus, the native client will be forced to do such a conversion.

A native client may be generous and do an implicit conversion for you, but equally the “least surprise element” that products tend to work for should assume that casting a SQL 2005 type that it does not understand should be rejected. There are some subtle mistakes that can shift from this for sure.

The same should be true in that you cast datetime2 (7) to SQL 2005, whether we expect it to round the 100ns precision to 3.33 ms or throw and make a mistake - I would prefer a mistake and make / accept an explicit cast.

Answer 2

I can only assume that this is because 2009-11-09 00:00:00.000 not time neutral and will cause more subtle errors. Please correct me if I am wrong.

Answer 3

You can achieve this using ALTER SESSION SET NLS_DATE_FORMAT = 'MM / DD / YYYY HH: MI: SS AM'; This is a temporary solution. If yu works on UNIX, these parameters can be executed continuously in .profile.