Using Sql-server Datetime2 With Tadoquery.open

I want to start using datetime2 fields in SQL Server and I need to insert into the tables via ADO from Delphi XE5. I am using TADOQuery.ExecSQL to insert and everything works fine.

Solution 1:

The problem is that datetime2 does not come back to ADO clients using the SQLOLEDB provider as the correct ADO data type (adDBTimestamp):

adDBTimeStamp (135)

Indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).

Instead it comes back as a unicode string (adVarWChar):

adVarWChar (202)

Indicates a null-terminated Unicode character string.

• 2016-11-03 12:06:01.0000000

SQL Server Native Client (SQLNCLI)

You could try switching to one of the "native" OLEDB providers (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11). The problem with those is that:

• the SQL Server Native client doesn't come with the operating system (you have to install it yourself on client PCs)
• the SQL Server Native Client is derpricated
• the SQLNCLI provider exposes XML columns as an unsupported ADO DataTypeEnum value (141, DBTYPE_XML)
• the SQLNCLI provider exposes TIME columns as an unsupported ADO DataTypeEnum value (145)
• the SQLNCLI provider exposes UDT columns as an unsupported ADO DataTypeEnum value (132)

You can use the DataTypeCompatibility=80 option in your connection string to cause XML columns to return as adLongVarWChar (as they do in SQLOLEDB), but then datetime2, date, and time return as strings

The problem with use DataTypeCompatibility=80 is that there's a bug in the native client driver. It mistakenly converts an SQL Server DATE column from adDBDate into adVarWChar:

|SQL Server data type | SQLOLEDB        | SQLNCLI            | SQLNCLI w/DataTypeCompatibilyt=80||----------------------|-----------------|--------------------|-----------------------------------|| Xml                  | adLongVarWChar  |141 (DBTYPE_XML)   | adLongVarChar                     || datetime             | adDBTimeStamp   | adDBTimeStamp      | adDBTimeStamp                     || datetime2            | adVarWChar      | adDBTimeStamp      | adVarWChar                        ||date| adVarWChar      | adDBDate           | adVarWChar                        ||time| adVarWChar      |145 (unknown)      | adVarWChar                        || UDT                  ||132 (DBTYPE_UDT)   | adVarBinary (documented,untested) ||varchar(max)         | adLongVarChar   | adLongVarChar      | adLongVarChar                     || nvarchar(max)        | adLongVarWChar  | adLongVarWChar     | adLongVarWChar                    ||varbinary(max)       | adLongVarBinary | adLongVarBinary    | adLongVarBinary                   ||timestamp| adBinary        | adBinary           | adBinary                          |

This bug in the SQL Native Client Provider was documented on MS Connect. But the person from Microsoft, not understanding what he was told, closed it as Won't fix.

So, if you really want to use a datetime2 from ADO, you will have to read it as a string, and parse it yourself.

Bonus Reading

• MSDN: Using ADO with SQL Server Native Client