If you try to use the new date/time data types in SQL 2008 with ADO and SQLNCLI10
you find that the only way to use time and datetimeoffset is to use the
connection property DatatatypeCompatibility=80. This is not surprising, as
these types are represented with type indicators unknown to ADO.
On the other hand, if you use date and datetime2 you find that these work
alright with ADO. Neither this is surprising, since this SQL Server types
are represented with standard OLE DB types. There is one small exception:
dates in the first century does not work. On input the dates are misinterepreted,
on output they cause a "multi-step operation generated errors". (I should
emphasize here that I assume that the application work with the dates as
strings, as this is how you typically works with dates in a GUI application.)
Very few applications use dates from the first century. But there are
plenty of applications out there that today use datetime for data that is date-only.
Many of applications will remain that way, even if they are migrated to SQL 2008.
But there will also be cases where you want to change these datetime column
to date. For instance, for the application I work with, this is an interesting option.
For this to work, though, the application must survive. If date was the only new
data type, we would only have to change the connection strings to use the
SQLNCLI10 provider. But if there is just a single corner of the application where some
data type appears, for instance the xml data type, we would need to use
Since date values now are returned as and received as nvarchar, this leads to a
localisation problem. A date like 1917-12-06 is returned like that, no matter the
setting. Our Finnish customers insists on seeing this as 6.12.1917. And if they
enter it, they would be very upset to find that it has been interpreted as 1917-06-12
which is what happens if SQL Server has us_english as the language.
While the application could take up the task to format and interpret the dates
correctly, this is not a viable option. We are after all talking about legacy code.
Thus, taking the step to use date would be a very fragile option, in case something
else would require us to use DatatypeCompatibility=80.