Thanks ever so much Helen for your Sunday afternoon sermon.

Much appreciated.

Rgds

Peter

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


At 01:27 PM 9/11/2003 +0000, you wrote:
>Hi Helen
>
>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.

You should use a TMaskEdit for the user input and convert it to a date/time
type before passing it **as a parameter** to the query. Delphi StrToDate
or StrToDateTime.


>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.

The main thing you have to be worried about is missing out on timestamp
dates that occur after midnight at the high end of your range, if you are
trying to compare timestamps to date-onlies. That means that "<=" to the
enddate won't do - it has to be "< enddate + 1".

Make the SQL
select id from pins
where status_changed >= :startdate
and status_changed < :enddate + 1


>I was then going to modify the WHERE SQL.

No, this is unnecessary unpreparing and preparing. You only have to
prepare the parameterised statement once. Then, after you close the
dataset for the search, do this just before you call Open:

Dataset.ParamByName('StartDate').AsDateTime := StrToDate(YourMaskEdit.Text);

Dataset.ParamByName('EndDate').AsDateTime := StrToDate(YourOtherMaskEdit.Text);

You can handle invalid date formats from the bleccch that emits from the
StrToDate function - that way you never need to send rubbish across the wire.

>I also plan to limit the number amount of data return, probably <500 records.

With 500 rows, it would probably be worthwhile indexing the date. Of
course, if you stick with all those casts, indexes wouldn't be used.

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

It doesn't get simpler than this. It's boilerplate code that you can add
to your "wash and wear" collection. Use a little no-brainer TMaskEdit
descendant for user date inputs cuz you can't never trust dem users.

Helen



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]