Home Dashboard Directory Help

The Datatypecompatibility property should not affect date and datetime2 by Erland Sommarskog


Status: 

Closed
 as Won't Fix Help for as Won't Fix


2
0
Sign in
to vote
Type: Suggestion
ID: 316457
Opened: 12/9/2007 7:49:16 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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
DatatypeCompatibility=80.

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.
Details
Sign in to post a comment.
Posted by Microsoft on 12/10/2007 at 1:42 PM
Thank you for your feedback.
The design of DataTypeCompatibility is intended to provide strict compatibility with SQL Server 2000 data types and adding additional settings or exempting some types would be difficult since it is partially implemented by SQL Server rather than the client and has a dependency on the TDS specification for older clients - which cannot be changed.
The year range of the new types was influenced by the SQL Standard and other products. Basing the year range on 100 rather than 1 would cause compatibility issues with other software. For example, some legacy applications use the date 1-1-1 as an alternative to NULL.
Legacy applications should work without problems with SQL Server 2008 providing there are no schema changes, and could made to work with the schema change you suggest with modest code change. It is not feasible to change SQL Native Client to enable the application to work without code change when the schema is changed.
Sign in to post a workaround.