Date problem in Firebird

Hi, I have a problem using dates in Firebird.
       T1:= StrToDate(MaskEdit3.Text);
       T2:= StrToDate(MaskEdit4.Text);

                  SQL.Text:= 'SELECT SUM(BEDRAGTOTAAL) AS SOM1 FROM KASSAVERKOPEN ' +
                             'WHERE ((FACTUURDATUM BETWEEN :pNu AND :pNi) ' +
                             'AND (KLANTID = :pKLANTID) AND (VOLDAAN = 1))';
                  ParamByName('pNu').AsDate:= T1;
                  ParamByName('pNi').AsDate:= T2;
                  ParamByName('pKLANTID').AsDate:= Temp1query1.FieldByName('KLANTID').AsInteger;;

Open in new window

I get an error when  (FACTUURDATUM BETWEEN :pNu AND :pNi) --> conversion error -303
"1899-12-31"

I tried tot use CAST, but the result is the same. What might be the problem here ?

thx
Mario
mariowayAsked:
Who is Participating?
 
Sinisa VukConnect With a Mentor Commented:
So, this is solved. But just for info about formatting in firebird:
http://mc-computing.com/Databases/delphi/Firebird/Dates.html
Although we can use:
ParamByName('....').AsDate ...
I prefer to use "native" string formatting in code and concatenate into sql string.
0
 
Nick UpsonPrincipal Operations EngineerCommented:
firebird doesn't like the format of the date string, is FACTUURDATUM a timestamp my any chance
0
 
mariowayAuthor Commented:
No , Date

       with Distinctfactuurklantidquery do begin
        Close;
         SQL.Text:= 'SELECT DISTINCT KLANTID FROM FACTUUR ' +
                    'WHERE ((VOLDAAN = 1) AND (FACTUURDATUM BETWEEN BETWEEN :pNu AND :pNi))';
         ParamByName('pNu').AsDate:= T1;
         ParamByName('pNi').AsDate:= T2;
        Open;
        First;
       end;

this query for instance works fine; strange .....
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mariowayAuthor Commented:
Sorry, I see now that I made an error

ParamByName('pKLANTID').AsDate:= Temp1query1.FieldByName('KLANTID').AsInteger;

Should be:   ParamByName('pKLANTID').AsInteger:= Temp1query1.FieldByName('KLANTID').AsInteger;;
0
 
Nick UpsonPrincipal Operations EngineerCommented:
if you replace the variables with hard-coded values does it work then, try several different formats and dates and post the results
0
 
mariowayAuthor Commented:
Nick, it works now, I coded: ParamByName('pKLANTID').AsDate, should be: ASINTEGER

Mario
0
 
Nick UpsonConnect With a Mentor Principal Operations EngineerCommented:
so basically wrong error message

btw: seems wierd you are selecting based on primary key (I assume pklandid) and other criteria at the same time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.