Adodb Unable To Store Datetime Value With Sub-second Precision

According to the Microsoft documentation for the DATETIME column type, values of that type can store 'accuracy rounded to increments of .000, .003, or .007 seconds.' According to t

Solution 1:

This is a well-known bug in the SQL Server OLEDB drivers going back more than 20 years; which means it is never going to be fixed.

It's also not a bug in ADO. The ActiveX Data Objects (ADO) API is a thin wrapper around the underlying OLEDB API. The bug exists is in Microsoft's SQL Server OLEDB driver itself (all of them). And they will never, never, never fix it now; as they are chicken-shits that don't want to maintain existing code it might break existing applications.

So the bug has been carried forward for decades:

• SQOLEDB (1999) → SQLNCLI (2005) → SQLNCLI10 (2008) → SQLNCLI11 (2010) → MSOLEDB (2012)

The only solution is rather than parameterizing your datetime as timestamp:

• adTimestamp (aka DBTYPE_DBTIMESTAMP, 135)

you need to parameterize it an "ODBC 24-hour format"yyyy-mm-dd hh:mm:ss.zzzstring:

• adChar (aka DBTYPE_STR, 129): 2021-03-21 17:51:22.619

or with even with the ADO-specific type string type:

• adVarChar (200): 2021-03-21 17:51:22.619

What about other DBTYPE_xxx's?

You might think that the adDate (aka DBTYPE_DATE, 7) looks promising:

Indicates a date value (DBTYPE_DATE). A date is stored as a double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.

But unfortunately not, as it also parameterizes the value to the server without milliseconds:

exec sp_executesql N'SELECT @P1 AS Sample',N'@P1 datetime','2021-03-21 06:40:24'

You also cannot use adFileTime, which also looks promising:

Indicates a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME).

Meaning it could support a resolution of 0.0000001 seconds.

Unfortunately by the rules of VARIANTs, you are not allowed to store a FILETIME in a VARIANT. And since ADO uses variants for all values, it throws up when it encounters variant type 64 (VT_FILETIME).

Decoding TDS to confirm our suspicions

We can confirm that the SQL Server OLEDB driver is not supplying a datetime with the available precision by decoding the packet sent to the server.

We can issue the batch:

SELECT ? AS Sample

And specify parameter 1: adDBTimestamp - 3/21/2021 6:40:23.693

Now we can capture that packet:

0000   0301007b00000100ffff0a0000000000...{............0010   63280000000904000132280000005300c(.......2(...S.0020   45004c00450043005400200040005000E.L.E.C.T..@.P.0030   31002000410053002000530061006d001..A.S..S.a.m.0040   70006c00650000006318000000090400p.l.e...c.......0050   01321800000040005000310020006400.2....@.P.1..d.0060   610074006500740069006d0065000000a.t.e.t.i.m.e...0070   6f0808f2ac000020f96d00o.......m.

And decode it:

03                  ; Packet type. 0x03 = 3 ==> RPC
01                  ; Status
007b               ; Length. 0x07B ==> 123 bytes
0000               ; SPID
01                  ; Packet ID
00                  ; Window
ff ff               ; ProcName 0xFFFF => Stored procedure number. UInt16 number to follow
0a 00               ; PROCID  0x000A ==> stored procedure ID 10 (10=sp_executesql)
0000               ; Option flags (16 bits)

00006328000000 09   ; blah blah blah 
0400013228000000   ; 

530045004c 004500   ; \  
4300540020004000   ;  |
5000310020004100   ;  |- "SELECT @P1 AS Sample"5300200053006100   ;  |
6d 0070006c 006500   ; /

00006318000000 09   ;  blah blah blah
0400013218000000   ;

4000500031002000   ; \
6400610074006500   ;  |- "@P1 datetime"740069006d 006500   ; /

00 00 6f 08 08      ; blah blah blah

f2 ac 00 00         ; 0x0000ACF2 = 44,274 ==> 1/1/1900 + 44,274 days = 3/21/202120 f9 6d 00         ; 0x006DF920 = 7,207,200 ==> 7,207,200 / 300 seconds after midnight = 24,024.000 seconds = 6h 40m 24.000s = 6:40:24.000 AM

The short version is that a datetime is specified on-the-wire as:

datetime is represented in the following sequence:

• One 4-byte signed integer that represents the number of days since January 1, > 1900. Negative numbers are allowed to represent dates since January 1, 1753.
• One 4-byte unsigned integer that represents the number of one three-hundredths of a second (300 counts per second) elapsed since 12 AM that day.

Which means we can read the datetime supplied by the driver as:

• Date portion: 0x0000acf2 = 44,274 = January 1, 1900 + 44,274 days = 3/21/2021
• Time portion: 0x006df920 = 7,207,200 = 7,207,200 / 300 seconds = 6:40:24 AM

So the driver cut off the precision of our datetime:

Supplied date:2021-03-21 06:40:23.693Date in TDS:2021-03-21 06:40:24

In other words:

• OLE Automation uses Double to represent datetime.

• The Double has a resolution to ~0.0000003 seconds.

• The driver has the option to encode the time down to 1/300th of a second:

  6:40:24.693 → 7,207,407 → 0x006DF9EF

But it chose not to. Bug: Driver.

Resources to help decoding TDS

• 2.2.6.6 RPC Request
• 4.8 RPC Client Request (actual hex example)
• 2.2.5.5.1.8 Date/Times