BorlandTalk.com

Crazy Horse's crazier lit · Guest

I must be missing something "obvious." With the following Delphi 7 code
using FIB+ components:

... first part of sql statement not shown
{ Date range }
FIBDataset.SQLs.SelectSQL.Add('and (p.PCHIN between :FromDate and
:ToDate) ');
FIBDataset.ParamByName('FromDate').AsDate := dtpFrom.Date; //4/5/2005
FIBDataset.ParamByName('ToDate').AsDate := dtpTo.Date; //4/5/2005

{ Time range }
FIBDataset.SQLs.SelectSQL.Add('and CAST(EXTRACT (HOUR FROM p.PCHIN) as
char(2)) between :FromHour and :ToHour ');
FIBDataset.SQLs.SelectSQL.Add('and CAST(EXTRACT (MINUTE FROM p.PCHIN)
as char(2)) between :FromMinute and :ToMinute ');

DecodeTime(dtpTimeFrom.DateTime, wHourFrom, wMinFrom, wSecFrom,
wMSecFrom);
DecodeTime(dtpTimeTo.DateTime, wHourTo, wMinTo, wSecTo, wMSecTo);
FIBDataset.ParamByName('FromHour').AsInteger := wHourFrom; //0
FIBDataset.ParamByName('ToHour').AsInteger := wHourTo; //23
FIBDataset.ParamByName('FromMinute').AsInteger := wMinFrom; //0
FIBDataset.ParamByName('ToMinute').AsInteger := wMinTo; //59
. . . rest of sql statement not shown

and one day set as both ends of the date range (e.g. 4/5/2005 and 4/5/2005),
no records are returned from the query.

This is even when the hours and minutes are left at their maximum range
(from 12:00 am to 11:59 pm), which equate to a range from 0:00 to 23:59.

There ARE records in the database for the dates I'm testing. Using 4/5/2005
to 4/5/2005 gives no records; 4/6/2005 to 4/6/2005 gives no records, but
4/5/2005 to 4/6/2005 returns 76 records.

....?

--

Download Blackbird Crow Raven's book
"STILL CASTING SHADOWS: Two American Families 1620-2006"
(.exe and Delphi source): http://cc.borland.com/ccweb.exe/listing?id=23106
--email me for the most up-to-date manuscripts

Eric Hill · Guest

I'm assuming you're using SQL Server.

The date field is actually a date/time field, so 4/5/2005 is internally
"4/5/2005 00:00" (i.e. midnight). 4/5/2005 to 4/5/2005 is just pulling
items that happen between midnight on the 5th and midnight on the 5th - - no
results.

FYI, If you're inserting data into the table with just a date, then all the
values will have midnight for the time portion, and your example query will
work. But typical applications use the current "system time" as a time
stamp, therefore keeping a proper time with the date.

You're really looking for 4/5/2005 00:00 through 4/5/2005 23:59. You can
use "4/5/2005 through 4/6/2005" if you don't care about including items that
happened at midnight on the 6th.

Eric

Bill Todd · Guest

What version of InterBase?

Dialect 1 or 3?

What is the data type of the column?

--
Bill Todd (TeamB)
TeamB cannot answer questions received via email

Eric Hill · Guest

Crazy Horse's crazier lit · Guest

"Bill Todd" <no (AT) no (DOT) com> wrote

Bill Todd · Guest

With a timestamp field the date and time are stored in a single field. You
need to use a single parameter for both the date and the time.

--
Bill Todd (TeamB)
TeamB cannot answer questions received via email

Crazy Horse's crazier lit · Guest

"Bill Todd" <no (AT) no (DOT) com> wrote

Crazy Horse's crazier lit · Guest

"Crazy Horse's crazier little brother" <cshannon (AT) d4sw (DOT) com> wrote

Bill Todd · Guest

In that case you need to store the date and time in separate fields of
the appropriate type. The only other alternative I can think of is an
OR in the WHERE for each date.

--
Bill Todd (TeamB)
TeamB cannot answer questions received via email

Barak zabari · Guest

I don't know about Firebird but in MSSQL you can do It this way

Select Expire_Date from insurance_from_Site
Where
Expire_Date-Cast(Cast(Expire_Date AS Char(11)) As DateTime) >='1900-01-01
01:00:00'
And
Expire_Date-Cast(Cast(Expire_Date AS Char(11)) As DateTime) <='1900-01-01
03:00:00'

The result will be all the rows in the table where the Time part of
expire_date field is between 1 am and 3 am

"Bill Todd"