Subject Re: SQL conundrum
Author delphigurusam
"maybe"... the problem is with the 12:00:00 AM. If you mean
mid-night, I believe it should be 00:00:00 AM. Noon is 12:00:00 PM.
So, if you are querying the time "between" two values, shouldn't the
first value be (valid) and smaller than the second? Just my 2 cents.
Sam

--- In firebird-support@yahoogroups.com, "Clay Shannon"
<cshannon@d...> 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 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]