Subject | Re: SQL conundrum |
---|---|
Author | delphigurusam |
Post date | 2005-04-07T22:26:23Z |
"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:
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 timerange
> within each date.at "swing
> IOW, they might select 4/1/2005 to 4/7/2005, but just want to look
> shift".p.pchout,
>
>
>
> This sql (in DB WB):
>
>
>
> select e.fullname, d.deptname, p.dayofweek, p.pchin, p.pchinloc,
> p.pchoutlocp.pchout,
> 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.pchoutloc from pchupp.pchdept
>
> p join empinfo e on e.empno = p.empno join dept d on d.deptno =
> where (id > 305) anderror code
>
> 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
> -303. Conversion error from string 1899-12-30" and then ""Incompatibleerror from
> column/host variable data type. SQL error code -303. Conversion
> 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]