I am trying to allow the user to select both a date range AND a time range
within each date.
IOW, they might select 4/1/2005 to 4/7/2005, but just want to look at "swing
shift".



This sql (in DB WB):



select e.fullname, d.deptname, p.dayofweek, p.pchin, p.pchinloc, p.pchout,
p.pchoutloc
from pchup p
join empinfo e on e.empno = p.empno
join dept d on d.deptno = p.pchdept
where (id > 305)
and cast(p.PCHIN as date) between '2005/4/1' and '2005/4/7'
and cast(p.PCHIN as time) between '12:00:00 am' and '11:59:00 pm'



--gives the err msg "conversion error from string "11:59:00 pm"".



If '11:59:00 pm' is wrong, why is '12:00:00 am' accepted?



==================
In my app (Delphi 7), the "same" SQL:



select e.fullname, d.deptname, p.dayofweek, p.pchin, p.pchinloc, p.pchout,
p.pchoutloc from pchup

p join empinfo e on e.empno = p.empno join dept d on d.deptno = p.pchdept
where (id > 305) and

cast (p.PCHIN as date) between :FromDate and :ToDate
and cast (p.PCHIN as time) between :FromTime and :ToTime



with these params (FIB+ component):



FIBDataset.ParamByName('FromDate').AsDate := Trunc(dtpDateFrom.Date);
FIBDataset.ParamByName('ToDate').AsDate := Trunc(dtpDateTo.Date);
FIBDataset.ParamByName('FromTime').AsTime := Frac(dtpTimeFrom.Date);
FIBDataset.ParamByName('ToDate').AsTime := Frac(dtpTimeTo.Date);



gives err msg "Incompatible column/host variable data type. SQL error code
-303. Conversion error from string 1899-12-30" and then ""Incompatible
column/host variable data type. SQL error code -303. Conversion error from
string 23:59:00.0000"



Why am I getting these explosions, and more importantly still, what do I
need to get the results I so strongly desire?





Clay Shannon,

Dimension 4 Software





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