Problem with SQLTimeStamp

[zvasku]

Hello,

I have problem with DATE=TSQlTimeStamp fields. This simple code generates exception:

var X:Variant;

X:=VarSQLTimeStampCreate(Now);
Q.SQL.Text:='insert into testa(id,datum) values (1,:D)';
Q.ParamByName('D').Value:=X;
Q.ExecSQL();
Q.SQL.Text:='update testa set id=2 where DATUM=:D';
Q.ParamByName('D').Value:=X;
r:=Q.ExecSQL();
if r=0 then raise Exception.Create('Record not found');

D2005, D2006, driver 4.20.5 OIC and NET too

Is it a problem in driver or is it a general problem of sqltimestamps?

Thanks
Zdenek

[Plash]

The problem occurs because of inaccuracy on conversion between TDateTime and TSQLTimeStamp. This is a usual occurrence for DateTime and floating point numbers.

You can avoid the problem if you will compare only date part of values:

Code: Select all

update testa set id=2 where trunc(DATUM) = trunc(:D)

The following query also should work:

Code: Select all

update testa set id=2
where to_char(DATUM, 'dd.mm.yyyy hh24:mi:ss') = to_char(:D, 'dd.mm.yyyy hh24:mi:ss')

There also was no problems in our tests, if TDateTime value is assigned to the parameter:

Code: Select all

var
  D: TDateTime; 
...
  D := Now; 
  Q.ParamByName('D').Value := D; 

[zvasku]

Thanks for you answer. I usually use TO_DATE/TO_CHAR conversion for such a problem. This problem essentialy comes from TClientDataset.Applyupdate where TSQLResolver generates where DATUM=? and then assign Value from Field which is TSQLTimeStamp. I remove field DATUM from where clause in such a case and all is ok now.

Zdenek