Short Version
Trying to pass the datetime value 12/30/1899 to SQL Server, fails with Invalid date format - but only for the native client drivers, and only in DataTypeCompatiblity mode.
Long Version
When trying to use parameterized queries in ADO, against SQL Server:
SELECT ?
I parameterize the datetime value as an adDBTimeStamp:
//Language agnostic, vaguely C#-like pseudo-code
void TestIt()
{
DateTime dt = new DateTime("3/15/2020");
VARIANT v = DateTimeToVariant(dt);
Command cmd = new Command();
cmd.CommandText = "SELECT ? AS SomeDate";
cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
Connection cn = GetConnection();
cmd.Set_ActiveConnection(cn);
cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
}
And that works fine when the date is 3/15/2020.
You create a VARIANT, with a VType of 7 (VT_DATE), and a value that is an 8-byte floating point value:
VARIANT
Int32 vt = 7; //VT_DATE
Double date = 0;
But it fails on 12/30/1899
If I do the same test code with one particular datetime, it fails:
void TestIt()
{
DateTime dt = new DateTime("12/30/1899");
VARIANT v = DateTimeToVariant(dt);
Command cmd = new Command();
cmd.CommandText = "SELECT ? AS SomeDate";
cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
Connection cn = GetConnection();
cmd.Set_ActiveConnection(cn);
cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
}
The ADO OLEDB provider throws an exception (i.e. before it even reaches SQL Server):
Invalid date format
But it doesn't happen with all SQL Server OLEDB providers
When debugging this issue, I realized it doesn't happen with all of the SQL Server OLEDB providers. Microsoft generally has 4 OLE DB Providers for SQL Server:
SQLOLEDB: Microsoft OLE DB Provider for SQL Server (has shipped with Windows since Windows 2000)SQLNCLI: SQL Server Native Client (shipped with SQL Server 2005)SQLNCLI10: SQL Server Native Client 11.0 (shipped with SQL Server 2008)SQLNCLI11: SQL Server Native Client 12.0 (shipped with SQL Server 2012)MSOLEDBSQL: Microsoft OLE DB Driver for SQL Server (shipped with SQL Server 2016)
When trying it with some different providers, it does work fine for some:
SQLOLEDB: WorksSQLNCLI11(without DataTypeCompatibility): WorksSQLNCLI11(with DataTypeCompatiility on): Fails
DataTypeCompatibility?
Yes. ActiveX Data Objects (ADO), a friendly COM wrapper around the unfriendly COM OLEDB API, doesn't understand the new date, time, xml, datetime2, datetimeoffset data types. New OLEDB data type constants were created to represents these new types. So any existing OLEDB applications wouldn't understand the new constants.
To that end, a new keyword is supported by the "native" OLE DB drivers:
DataTypeCompatibility=80
which you can add to your connection string:
"Provider=SQLNCLI11; Data Source=screwdriver; User ID=hatguy; Password=hunter2;DataTypeCompatibility=80;"
This instructs the OLEDB driver to only return OLEDB data types that were in existance when OLEDB was first invented:
| SQL Server data type | SQLOLEDB | SQLNCLI | SQLNCLI (w/DataTypeCompatibility=80) |
|---|---|---|---|
| Xml | adLongVarWChar | 141 (DBTYPE_XML) | adLongVarChar |
| datetime | adDBTimeStamp | adDBTimeStamp | adDBTimeStamp |
| datetime2 | adVarWChar | adDBTimeStamp | adVarWChar |
| datetimeoffset | adVarWChar | 146 (DBTYPE_DBTIMESTAMPOFFSET) | adVarWChar |
| date | adVarWChar | adDBDate | adVarWChar |
| time | adVarWChar | 145 (DBTYPE_DBTIME2) | 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 |
And there's the failure
When:
- trying to parameterize a
datetimevalue - with a value of
12/30/1899 - when using a "native client" driver
- and
DataTypeCompatilibtyis on - the driver itself chokes on the value
- when its value is, in fact perfectly fine.
There's nothing inherently wrong with trying to use a date of '12/30/1899`:
SELECT CAST('18991230' AS datetime)works fine- it works fine in the original OLE DB driver
- it works fine in the "native" OLE DB drivers
- it just fails in the native driver with
DataTypeCompatibilityon
Obviously this is a bug in Microsoft OLE DB drivers. But it's an absolute truth that Microsoft will never, ever, ever, EVER, fix the bug.
So how to work-around it?
I can detect this special datetime, and I can try to work around this bug in our data access layers.
- But I need a value I can place into a
VARIANTstructure, - that represents
12/30/1899 12:00:00 AM - that works under
SQOLEDB - and under
SQLNCLIxx drivers - and under
MSOLEDBSQLdriver - in
DataTypeCompatibilityMode - (and what the hell, even with the mode off - although it's invalid to use ADO without it on)
T-SQL generated by the driver
When the OLE DB driver does bother to actually do what i say, we can profile the RPC generated:
SQOLEDB
exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime','1899-12-30 00:00:00'
SQLNCLI11
exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime2(0)','1899-12-30 00:00:00'
CMRE (Delphi)
program Project1;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils,
ComObj,
ActiveX,
ADOdb,
ADOint,
Variants;
function GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;
var
connectionString: string;
begin
{
SQLOLEDB - Default provider with Windows
SQLNCLI11 - SQL Server 2008 native client
}
connectionString := 'Provider='+Provider+'; Data Source=screwdriver;User ID=hydrogen;Password=hunter2;';
if DataTypeCompatibility then
connectionString := connectionString+'DataTypeCompatibility=80';
Result := CoConnection.Create;
Result.Open(connectionString, '', '', adConnectUnspecified);
end;
procedure Test(ProviderName: string; DataTypeCompatibility: Boolean);
var
dt: TDateTime;
v: OleVariant;
cmd: _Command;
cn: _Connection;
recordsAffected: OleVariant;
s: string;
begin
dt := EncodeDate(1899, 12, 30);// 12/30/1899 12:00:00 AM (also known in Delphi as zero)
v := dt; //the variant is of type VT_DATE (7)
cmd := CoCommand.Create;
cmd.CommandText := 'SELECT ? AS SomeDate';
cmd.Parameters.Append(cmd.CreateParameter('', adDBTimeStamp, adParamInput, 0, v));
try
cn := GetConnection(ProviderName, DataTypeCompatibility);
except
on E: Exception do
begin
WriteLn('Provider '+ProviderName+' not installed: '+E.message);
Exit;
end;
end;
if SameText(ProviderName, 'SQLOLEDB') then
s := ''
else if DataTypeCompatibility then
s := ' (with DataTypeCompatibility)'
else
s := ' (without DataTypeCompatibility)';
cmd.Set_ActiveConnection(cn);
try
cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);
WriteLn('Provider '+ProviderName+s+': success.');
except
on E:Exception do
begin
WriteLn('Provider '+ProviderName+s+' failed: '+E.Message);
end;
end;
end;
procedure Main;
begin
CoInitialize(nil);
Test('SQLOLEDB', False); //SQL Server client that ships with Windows since 2000
Test('SQLNCLI', False); //SQL Server 2005 native client
Test('SQLNCLI', True); //SQL Server 2005 native client, w/ DataTypeCompatibilty
Test('SQLNCLI10', False); //SQL Server 2008 native client
Test('SQLNCLI10', True); //SQL Server 2008 native client, w/ DataTypeCompatibilty
Test('SQLNCLI11', False); //SQL Server 2012 native client
Test('SQLNCLI11', True); //SQL Server 2012 native client, w/ DataTypeCompatibilty
Test('MSOLEDBSQL', False); //SQL Server 2016 native client
Test('MSOLEDBSQL', True); //SQL Server 2016 native client, w/ DataTypeCompatibilty
end;
begin
try
Main;
except
on E: Exception do
Writeln(E.ClassName, ': ', E.Message);
end;
WriteLn('Press enter to close');
ReadLn;
end.
And while this is not a Delphi-specific question; I am using Delphi. So it's tagged as Delphi. If you complain I'm going to choke your tongue out.
Note: This is not ADO.net, it is ADO. It is not managed .NET Framework Class Library, it is the native Win32 COM OLE DB API.
