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.
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;
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
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): FailsYes. 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=80which 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 |
When:
datetime value12/30/1899DataTypeCompatilibty is onThere's nothing inherently wrong with trying to use a date of '12/30/1899`:
SELECT CAST('18991230' AS datetime) works fineDataTypeCompatibility onObviously 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.
I can detect this special datetime, and I can try to work around this bug in our data access layers.
VARIANT structure,12/30/1899 12:00:00 AMSQOLEDBSQLNCLIxx driversMSOLEDBSQL driverDataTypeCompatibilityModeWhen 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'
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.
BrakNicku had the answer.
Set
NumericScaleproperty of your parameter to anything in 1-7 range.
Changing the code from:
Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
to
Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;
works.
It even works with SQLOLEDB driver against SQL Server 2000.
Returning rowsets from SQL Server containing different data types, i can ask OLEDB what the Precision, and NumericScale of various T-SQL data types are:
SQL Server type ADO type Precision NumericScale DefinedSize
---------------- --------------------- --------- ------------ -----------
int adInteger (3) 10 255 4
real adSingle (4) 7 255 4
money adCurrency (6) 19 255 8
bit adBoolean (11) 255 255 2
tinyint adUnsignedTinyInt (17) 3 255 1
bigint adBigInt (20) 19 255 8
uniqueidentifier adGUID (72) 255 255 16
char(35) adChar (129) 255 255 35
nchar(35) adWChar (130) 255 255 35
decimal(15,5) adNumeric (131) 15 5 19
datetime adDBTimeStamp (135) 23 3 16
varchar(35) adVarChar (200) 255 255 35
text adLongVarChar (201) 255 255 2147483647
varchar(max) adLongVarChar (201) 255 255 2147483647
nvarchar(35) adVarWChar (202) 255 255 35
nvarchar(max) adLongVarWChar (203) 255 255 1073741823
xml adLongVarWChar (203) 255 255 1073741823
image adLongVarBinary (205) 255 255 2147483647
varbinary(max) adLongVarBinary (205) 255 255 2147483647
Since SQL Server returns a datetime field with a NumericScale of 3; there may be a virtue in changing:
Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;
to
Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 3;
Don't ever, ever, EVER try to parameterize a datetime as adDBTimestamp. There are data-loss bugs in Microsoft's SQL Server OLEDB drivers (all of them):
The correct answer is to parameterize all datetime values as a string (e.g. adVarChar) using the "ODBC 24-hour format":
yyyy-mm-dd hh:mm:ss.zzz2021-03-21 18:16:22.619