Thanks

Luc, Good advice from you and Helen, I'll have a play around with it later after I have watched Harry Potter 2 again.

Rgds

Peter

----- Original Message -----
From: Lucas Franzen
To: IBObjects@yahoogroups.com
Sent: Sunday, November 09, 2003 2:31 PM
Subject: Re: [IBO] Dynamic SQL



Peter,

peter@... schrieb:

> The status_changed field is a Firebird Timestamp , so it has both
> date and time info in it. e.g. 29/10/2003 19:58:41 The other date
> field can be of any data type as it will be entered via a user
> dialogue.
>
> I assumed obviously wrongly that I had to convert a Timestamp field
> to a Date and that the user input had to be of the same Date type to
> perform the comparison. I was then going to modify the WHERE SQL. I
> also plan to limit the number amount of data return, probably <500
> records.

When dealing with timestamps and asking for a daterange it's usually a
good idea to use two TDateTimePickers (or sth. like this) and set the
params within your sql like:

ParamByName ( 'StartDate' ).AsdateTime := trunc ( dtpStart.Date + 1 );
ParamByName ( 'EndDate' ).AsdateTime := trunc ( dtpEnd.Date + 1 );

as Helen adviced.


This way you can be sure that all dates between midnight of the
startdate and before the start of the enddate are within your select.

As do this quite often with dialect 1 databases and it's working
perfectly well.

If you want to restrict your query to 500 records simply enter 500 in
the MaxRows property.
But be aware that you haven't got a chance to get the "missing" records
then.
And if you've got ordering links and re-order your query it might happen
that you've got up to 500 different recors then.

>
> What I was trying to achieve was a simple master detail sales report
> between two dates.

Never let the user enter anything in a timestamp field with a user dialogue.
You can't be sure what they did.
For example if they enter 10/5/2003 what's it then?
The 5th of October or the 10th of May??
(this might depend on user settings for DateTime)

If you use datetime-components and insert into your table with
parameters you can be sure it will be saved appropriately.

Luc.


Yahoo! Groups Sponsor
ADVERTISEMENT




___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]