Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

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
0
marioway
Asked:
marioway
  • 3
  • 3
2 Solutions
 
NickUpsonSenior Network 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
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
NickUpsonSenior Network 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
 
NickUpsonSenior Network 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
 
Sinisa VukCommented:
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now