Errror setting date value less then '01.01.1753'

[Aufhauser]

I have problems setting a date value less then 01.01.1753.
FInsertAtt.Params[0].AsDateTime:= StrToDate('01.01.1753') works fine,
FInsertAtt.Params[9].AsDateTime:= StrToDate('31.12.1752') causes an error.
Can you please check this?

Stefan

[Ludek]

there's nothing, that devart people could do with this, as it is the limitation of sql server. see http://msdn.microsoft.com/en-us/library/ms187819.aspx

[Ludek]

btw. if works for me fine, if there's a datetime2 column in the table being accessed (if sdac is new enough). datetime2 allows dates < 1.1.1753.

[azyk]

The error is generated by SQL Server. The datetime data type does not accept dates earlier than January 1, 1753. More details about the datetime data type can be found out at the following SQL Server documentation section: http://technet.microsoft.com/en-us/libr ... .105).aspx

[Aufhauser]

Thank you for answers, but let me ask my question in an other way:
In SQLServer the field is defined as (SQLServer)date, so it can hold values less then 01.01.1753. Setting the date-value via ManagementStudio works fine!
The date-functions of SDAC(AsDate,AsDateTime,AsTime) set/get a (Delphi)TDateTime, which also can hold values less then 01.01.1753.
So: how can I set a date less then 01.01.1753 via SDAC?
AsDate, AsDateTime and AsTime or using Variant (Value) cause the same error (wrong date format).

Stefan

[azyk]

This behaviour is due to the specificity of the OLEDB provider. We can't influence this behaviour. To solve the problem, use SQL Native Client provider. For this, in connection settings set the option TMSConnection.Options.Provider to prNativeClient.

[Aufhauser]

I use NativeClient!

Stefan

[Ludek]

and do you use newest sdac? version 5 has problems...

[azyk]

We couldn't reproduce the problem. Supply us the following information
- Exact version of Delphi
- Exact version of SDAC. You can see it in the About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server. You can see it the Info sheet of TMSConnection Editor

[Aufhauser]

I use Delphi XE6, SQLServer 2014 SP2, SDAC 6.1.20, German format settings

in SQLManagementStudio:
CREATE TABLE dbo.DATE_TEST
(
ID INT NOT NULL IDENTITY (1, 1),
A_DATE DATE,
)
GO

in Delphi:
var
InsertDate: TCustomDASQL;
begin
InsertDate:= TMSSQL.Create(nil);
InsertDate.Connection:= MSConnection1;
InsertDate.SQL.Add('Insert into dbo.DATE_TEST Values(:1)');
InsertDate.Params[0].DataType:= ftDate;
InsertDate.Prepare;
//
InsertDate.Params[0].AsDate:= StrToDate('31.12.1752');
InsertDate.Execute;
End;

This code causes an error, if I comment out the lines
// InsertDate.Params[0].DataType:= ftDate;
// InsertDate.Prepare;
the code works fine, so the problem only occours, when using prepared parameters.

[azyk]

The reason is behavior of OLE DB, which we cannot influence. The dbGo components also return an error in the described case on using prepared dataset, for both OLEDB and SQL Native Client provider.