Board index » delphi » SQL date/time range problem
Crazy Horse's crazier little brother
Delphi Developer |
SQL date/time range problem2005-04-07 05:29:55 AM delphi132 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 am 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): cc.borland.com/ccweb.exe/listing --email me for the most up-to-date manuscripts |