SQL error "Value exceeds the range for valid timestamps"

Hello,

I have a strange problem.

In Delphi I execute the following source :

const
   CST_DUREEINTERV       = 1/24;
var
  i:integer;
  MaDateIntervention,dummy:Tdatetime;
  myYear,myMonth, myDay,MyHour,MyMinute,MySecond,MyMilliseconde:word;
  ValeurPrecise:double;
begin
     MaDateIntervention :=  now;
     DecodeDate(MaDateIntervention, myYear, myMonth, myDay);
     MaDateIntervention := EncodeDateTime(myYear, myMonth, myDay, 9, 00, 00, 00);



     for i:=0 to 20 do
     begin
        caption:=i.tostring;
        MaDateIntervention  := MaDateIntervention + CST_DUREEINTERV;


       IBCQuery1.ParamByName('MyDate').DataType:=  ftDateTime;
       IBCQuery1.ParamByName('MyDate').asdatetime:= MaDateIntervention;

       IBCQuery1.ExecSQL;

     end;



end;

When i=14 Firebird will return an SQL Error :
"value exceeds the range for valid timestamps"

Ok the problem is that the date (MaDateIntervention ) value is correct for Delphi but not for Firebird so the solution is to do to use IncHour like this :

const
   CST_DUREEINTERV       = 1/24;
var
  i:integer;
  MaDateIntervention,dummy:Tdatetime;
  myYear,myMonth, myDay,MyHour,MyMinute,MySecond,MyMilliseconde:word;
  ValeurPrecise:double;
begin
     MaDateIntervention :=  now;
     DecodeDate(MaDateIntervention, myYear, myMonth, myDay);
     MaDateIntervention := EncodeDateTime(myYear, myMonth, myDay, 9, 00, 00, 00);



     for i:=0 to 20 do
     begin
        caption:=i.tostring;
        MaDateIntervention:=incHour(MaDateIntervention,1);
       // MaDateIntervention  := MaDateIntervention + CST_DUREEINTERV;

       IBCQuery1.ParamByName('MyDate').DataType:=  ftDateTime;
       IBCQuery1.ParamByName('MyDate').asdatetime:= MaDateIntervention;

       IBCQuery1.ExecSQL;

     end;



end;

But in scripter use IncHour doesn't solve the problem.

Of course do this :

      DecodeDateTime(MaDateIntervention, myYear, myMonth, myDay,MyHour,MyMinute,MySecond,MyMilliseconde);
      MaDateIntervention := EncodeDateTime(myYear, myMonth, myDay, MyHour, MyMinute, MySecond,MyMilliseconde);

But why I need to do it ?

I don't know, IncHour works just fine here. This script works correctly:

    ShowMessage(DateTimeToStr(IncHour(Now, 1)));

I guess you have to investigate the FireDAC/Firebird side.

Hello Wagner,

I guess you have to investigate the FireDAC/Firebird side

I have done it. I use IBDAC.

But with Delphi this fix the problem if I replace :

MaDateIntervention  := MaDateIntervention + 1/24;

by

MaDateIntervention:=incHour(MaDateIntervention,1);

But with Scripter IncHour doesn't fix the problem !

Does it because all variable are variant ?

Actually it already doesn't make sense that you have to do such thing in Delphi. Both lines should be equivalent, so there is clearly a problem somewhere else.

Hello Wagner,

I'am not agree

Inchour(MyDate,1)

is not equal to

MyDate:=MyDate+1/24

Just add this :

    MaDateInterventionIncHour:=incHour(MaDateInterventionIncHour,1);
    MaDateIntervention  := MaDateIntervention + CST_DUREEINTERV;

Memo1.lines.add(FormatFloat('inchour=0.00000000000000',MaDateInterventionIncHour)+'   |   '+FormatFloat('add 1/24=0.00000000000000',MaDateIntervention));

And you obtain :

inchour=44510,41666666666420   |   add 1/24=44510,41666666666420
inchour=44510,45833333333580   |   add 1/24=44510,45833333332850
inchour=44510,50000000000000   |   add 1/24=44510,49999999999270
inchour=44510,54166666666420   |   add 1/24=44510,54166666665700
inchour=44510,58333333333580   |   add 1/24=44510,58333333332120
inchour=44510,62500000000000   |   add 1/24=44510,62499999998540
inchour=44510,66666666666420   |   add 1/24=44510,66666666664970
inchour=44510,70833333333580   |   add 1/24=44510,70833333331390
inchour=44510,75000000000000   |   add 1/24=44510,74999999997820
inchour=44510,79166666666420   |   add 1/24=44510,79166666664240
inchour=44510,83333333333580   |   add 1/24=44510,83333333330670
inchour=44510,87500000000000   |   add 1/24=44510,87499999997090
inchour=44510,91666666666420   |   add 1/24=44510,91666666663510
inchour=44510,95833333333580   |   add 1/24=44510,95833333329940
inchour=44511,00000000000000   |   add 1/24=44510,99999999996360

But now I have found my problem.
In my software IncHour use JvJCLUtils.pas unit which doesn't work like IncHour in DateUtils.pas

1 Like

Well, that's a double-precision problem, and Scripter indeed uses Variant types under the hood, not real double.