At 04:46 PM 7/04/2005 -0500, you wrote:

>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 fact, neither is accepted, because they are not valid time
literals. I'd suppose that the status array returned an exception
pertaining to the whole expression and your interface shows you the last
exception. Test that by running a query in isql with just your "... am"
expression in the predicate.

A time literal has to be of the format 'hh:nn:ss.xxxx'.

>==================
>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"

Trunc() and Frac() are Delphi functions and they operate on reals, not
DateTimes.

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

Trying to operate on date and time parts between Delphi and a RDMBS (other
than Paradox!) is a fraught exercise. Create TDateTime variables for the
start and end dates, get those variables populated, and then use
DecodeDateTime and EncodeDateTime to construct valid time literals to apply
to your FromTime and ToTime parameters. This isn't a Delphi forum so I
won't do the examples and research for you; but consult the Delphi help
regarding DecodeDateTime and EncodeDateTime.

Note, however, that **almost certainly** your DA interface (FIBPlus) would
already have utility functions to help you encode TDateTimes into valid
date-time literals. If it doesn't, roll your own and put it into a
reusable utility unit that you can include in all your projects. It's
something every Delphi programmer outside of the USA has to do as a matter
of course, since Borland software has never been friendly toward non-U.S.
date formats. But, as you've found, even U.S. programmers will sometimes
find ways to get stung, so it's worth the effort, even for you.

./hb