Errror setting date value less then '01.01.1753'

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Aufhauser
Posts: 56
Joined: Mon 21 Nov 2005 09:44
Location: Herzogenburg, Austria

Errror setting date value less then '01.01.1753'

Post by Aufhauser » Tue 08 Jul 2014 19:36

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
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Re: Errror setting date value less then '01.01.1753'

Post by Ludek » Wed 09 Jul 2014 07:57

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
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Re: Errror setting date value less then '01.01.1753'

Post by Ludek » Wed 09 Jul 2014 08:16

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Errror setting date value less then '01.01.1753'

Post by azyk » Wed 09 Jul 2014 08:28

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
Posts: 56
Joined: Mon 21 Nov 2005 09:44
Location: Herzogenburg, Austria

Re: Errror setting date value less then '01.01.1753'

Post by Aufhauser » Wed 09 Jul 2014 14:12

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Errror setting date value less then '01.01.1753'

Post by azyk » Thu 10 Jul 2014 12:18

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
Posts: 56
Joined: Mon 21 Nov 2005 09:44
Location: Herzogenburg, Austria

Re: Errror setting date value less then '01.01.1753'

Post by Aufhauser » Fri 11 Jul 2014 09:57

I use NativeClient!

Stefan

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Re: Errror setting date value less then '01.01.1753'

Post by Ludek » Fri 11 Jul 2014 15:14

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

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Errror setting date value less then '01.01.1753'

Post by azyk » Mon 14 Jul 2014 09:20

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
Posts: 56
Joined: Mon 21 Nov 2005 09:44
Location: Herzogenburg, Austria

prolem solved

Post by Aufhauser » Mon 21 Jul 2014 13:36

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Errror setting date value less then '01.01.1753'

Post by azyk » Tue 22 Jul 2014 14:38

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.

Post Reply