Patrick wrote:
>This is something I just discovered in a report I was doing that uses SQL
>queries running on Paradox tables.
>One of the options when choosing data for the report is to have a range of
>dates with the range being set by a pair of TDateTimePicker controls.
These
>become the inputs to a parameterised query using the Date property of the
>TDateTimePicker converting it to the TParam in the query.
>I discovered that using a range of only one day, records with the date of
>that day were not selected but if the range was extended a day in one
>direction, then records would be selected.
>The query worked correctly using literals both in DBD and Delphi and the
>next step was to look at the value of the Date property of the TDateTime.
It
>turns out that this is actually the Date and Time parameters combined (in
>spite of the fact that these are separate properties). Since I was using
the
>.AsDateTime property of the TParam to convert it, that only leaves me to
>speculate that the time information was input to the query and resulted in
>the records being rejected because the Date field in each record would
>presumably have the Time portion set to zero when it is read into the
query.
>The solution which has worked has been to change .AsDateTime to .AsDate
>wherever necessary.
>Anyone encounter a scenario like this before? Since I am using D3 it may
>well have been addressed in D4 or later.
My suspicion is the TDateTimePicker. Looks like its returning Date & Time
when Date is selected in "Kind". Your setting of parameter using asdate is
the way to go - but you don't need it universally.
Using two edit fields followed by strtodate works fine with parameters set
asdatetime and defined as datetime in the query.
Also Params[0].asdatetime := strtodate(datetostr(DateTimePicker1.Date));
works fine.
Jeremy suggests converting dates to strings and building the SQL statement
that way. My experience with that hasn't been good - it seems that in string
format, SQL dates are expected to be in "mm/dd/yy" format in WHERE clauses
regardless of your short date format. (Or is this just me and I've missed a
config parameter somewhere?)
HTH,
Mike
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
