Re: Leap Year Problem?

["Jeff T. Orwick [C]" <orwick@CIG.MOT.COM>]

DON'T USE TRUNC on dates. Any function on a column throws away any indexes that
is available for use now or in the future.  Instead use to_date on your
variables to insure they cover the entire day in your range.



On Apr 3, 12:50pm, Wes Bailey wrote:
> Subject: Re: Leap Year Problem?
> Due to the timestamp on the date in the database, all the dates on the
> 29th are being lost because the following example relation is true:
>
>        j1.effdt              $end_date
>        1996:04:04:08:00:00 > 1996:04:04:00:00:00
>
> using the TRUNC function should eliminate your problem.  Here is how
> the WHERE clause should be written:
>
> where ((trunc(j1.effdt) between to_date($start_date)
>                                 and
>                                 to_date($end_date)))
>    or ((trunc(j1.action_dt) between to_date($start_date)
>                                     and
>                                     to_date($end_date)))
>    or ((trunc(j1.effdt) between to_date($end_dt_minus_2yrs)
>                                 and
>                                 to_date($end_date)))
> ...
>
> Wes Bailey
> Consultant
> Ray Ontko & Co.
>
> ----------------------------------------------------------------------
> > Subject: Leap Year Problem?
> > Greetings from southeastern Ohio.
> >
> > We are running SQRW V2.3.1 on Oracle7.0.15.
> >
> > We have an SQR that reports employee status changes, like transfers
> > and promotions, between a user specified start date and end date.
> >
> > When the user answers the related start date and end date prompts with
> > 01-FEB-96 and 29-FEB-96 respectively, the query returns about half of the
> > records it should return. The user gets all records by running the query
> twice;
> > first with the 01-FEB-96 start date and 28-FEB-96 end date; and then with a
> > start date AND end date of 29-FEB-96. Does this have anything to do with
leap
> > year?
> >
> > Here is the code:
> >
> > FROM ps_personal_data p1, ps_job j2, ps_job j
> > WHERE ((j1.effdt between $start_date and $end_date)
> >    or ((j1.action_dt between $start_date and $end_date)
> >    and (j1.effdt between $end_dt_minus_2yrs and $end_date)))
> >   AND j1.action = $transfer
> >   AND length(j1.deptid) = 6
> >   AND length(j2.deptid) = 6
> >   AND ((substr(j1.deptid,1,3) = $SlctLoc) or
> >        (substr(j2.deptid,1,3) = $SlctLoc))
> >   AND j2.emplid = j1.emplid
> >   AND j2.effdt = (select MAX(j3.effdt)
> >                   from ps_job j3
> >                   where j3.emplid  = j2.emplid
> >                     and j3.deptid <> j1.deptid
> >                     and j3.effdt  < j1.effdt)
> >   AND j2.effseq = (select MAX(j4.effseq)
> >                    from ps_job j4
> >                    where j4.emplid = j2.emplid
> >                      and j4.deptid = j2.deptid
> >                      and j4.effdt  = j2.effdt)
> >   AND p1.emplid = j1.emplid
> > ORDER BY j1.effdt, j1.effseq desc, j1.emplid
> > END-SELECT
> >
> > Any ideas?
> >
> > Thanks,
> >
> > John Douglas
> > Lockheed Martin Utility Services
> > Piketon, Ohio
>-- End of excerpt from Wes Bailey



--
Jeff T. Orwick            orwickj@tusc.com <- Full time address
http://www.tusc.com