Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date problem in Firebird

Posted on 2013-12-21
8
Medium Priority
?
761 Views
Last Modified: 2013-12-26
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
Comment
Question by:marioway
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 39734366
firebird doesn't like the format of the date string, is FACTUURDATUM a timestamp my any chance
0
 

Author Comment

by:marioway
ID: 39734554
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
 

Author Comment

by:marioway
ID: 39734559
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 19

Expert Comment

by:NickUpson
ID: 39734571
if you replace the variables with hard-coded values does it work then, try several different formats and dates and post the results
0
 

Author Comment

by:marioway
ID: 39734581
Nick, it works now, I coded: ParamByName('pKLANTID').AsDate, should be: ASINTEGER

Mario
0
 
LVL 19

Assisted Solution

by:NickUpson
NickUpson earned 500 total points
ID: 39734626
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
 
LVL 27

Accepted Solution

by:
Sinisa Vuk earned 500 total points
ID: 39735568
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question