Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Date comparisons using parameterized query


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 7 - Last Post: Dec 4, 2017 5:51 AM Last Post By: Mark Williams
Mark Williams

Posts: 120
Registered: 5/8/10
Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 11:56 AM
I am trying to carry out a date comparison in a query where the date to be compared is parameterized as follows:

FDQuery.SQL.Text:=' Select * FROM documents WHERE last_update> :LastUpdate';
FDQuery.ParamByName('LastUpdate').AsDateTime:=strToDateTime('01/12/2017 12:15:56');


last_update is a datetime field.

If I don't use a parameter for the date field ie

FDQuery.SQL.Text:=' Select * FROM documents WHERE last_update>'01/12/2017 12:15:56';


it works as expected.

I have tried setting the date parameter as a string:

FDQuery.ParamByName('LastUpdate').Asstring:='01/12/2017 12:15:56';


but this also does not work.

This is a query that will be run frequently and the date parameter will change each time it is run. I would like to prepare the query at the outset (the actual query is much more complex than the above) and work with a date parameter

Is this possible? How do I do it?
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 12:57 PM   in response to: Mark Williams in response to: Mark Williams
Mark Williams wrote:

I am trying to carry out a date comparison in a query where the date
to be compared is parameterized as follows:

FDQuery.SQL.Text:=' Select * FROM documents WHERE last_update>
:LastUpdate';
FDQuery.ParamByName('LastUpdate').AsDateTime:=strToDateTime('01/12/201
7 12:15:56'); 


last_update is a datetime field.

If I don't use a parameter for the date field ie

FDQuery.SQL.Text:=' Select * FROM documents WHERE
last_update>'01/12/2017 12:15:56'; 


it works as expected.

I have tried setting the date parameter as a string:

FDQuery.ParamByName('LastUpdate').Asstring:='01/12/2017 12:15:56';


but this also does not work.

I've tried using XE5 and Informix database:

FDQuery.ParamByName('LastUpdate').AsDateTime:=EncodeDate(2017,12,01)+Enc
odeTime(12,15,56,0);

Worked as expected.

Mark Williams

Posts: 120
Registered: 5/8/10
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 1:08 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
I've tried using XE5 and Informix database:

FDQuery.ParamByName('LastUpdate').AsDateTime:=EncodeDate(2017,12,01)+Enc
odeTime(12,15,56,0);

Worked as expected.

Can you please try by assigning a TDateTime value rather than encode date? That also didn't work for me.

I am using 10.2 btw.

I will in the meantime try encodeDate and see if that works.

Thanks
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 2:17 PM   in response to: Mark Williams in response to: Mark Williams
Mark Williams wrote:

I've tried using XE5 and Informix database:

FDQuery.ParamByName('LastUpdate').AsDateTime:=EncodeDate(2017,12,01)
+Enc odeTime(12,15,56,0);

Worked as expected.

Can you please try by assigning a TDateTime value rather than encode
date? That also didn't work for me.

It's working for me. I've tried:

var x: TDateTime;
begin
  x:= EncodeDate(2017,12,01)+EncodeTime(12,15,56,0);
  FDQuery.ParamByName('LastUpdate').AsDateTime:=x;
end;


You should try to trace your query on the server.

Mark Williams

Posts: 120
Registered: 5/8/10
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 2:56 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
You should try to trace your query on the server.

It doesn't work for me even with encodeDate+encodeTime.

Will do as you suggest and see what's happening on the server and update the thread, but thanks for your help so far.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 10:51 PM   in response to: Mark Williams in response to: Mark Williams
You can produce FireDAC trace output and post it to attachments forum. Then I will be able to check ...

--
With best regards,
Dmitry
Mark Williams

Posts: 120
Registered: 5/8/10
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2017 1:07 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
You can produce FireDAC trace output and post it to attachments forum. Then I will be able to check ...

--

I had read about and completely forgotten bout the trace features! Awesome! Could I make a small request? Could you add a line break between each record in the flat file to make it a little easier on the eye?

That aside, having added a flatfile trace to my app, the query is now working as expected without any other change of code. Something else must be going on somewhere so I will continue to dig around and update the thread when I have found the answer
Mark Williams

Posts: 120
Registered: 5/8/10
Re: Date comparisons using parameterized query  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 4, 2017 5:51 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Found the issue. I am using the same query object to check for updated records. I was not setting its active property to false before the next call to open.

Thanks for help.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02