Re: Leap Year Problem?

[Wes Bailey <wesb@ONTKO.COM>]

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