Re: Leap Year Problem?

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

Do you have this same problem for any other date ranges?  And what's the date
on the missing records if you specify the full range of dates.  You may want to
consider doing an explicit to_date on your end_date value to force the date to
the last second of Feb-29  (i.e. to_date($end_date||' 23:59:59','DD-MON-RR
HH24:MI:SS')  ) If you don't specify the hours minutes seconds oracle converts
it to 00:00:00 of that day.  Remember oracle dates always store the time in the
database.  If the field you're checking was populated by SYSDATE it will have
time in it and not be defaulted to 00:00:00.

Because 29-FEB-96 00:00:00 occurs before 29-FEB-96 08:00:00 these records would
NOT be returned.

That said, I've also noticed that when using between and dates that or the same
day on each side without specifying times seems to return everything that
happend that day.

Hope this helps you.

Jeff Orwick TUSC

On Apr 3,  9:17am, John Douglas wrote:
> 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 John Douglas



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