Re: Leap Year Problem?

[johnk@SUN4.MITI.COM]

     Do you get the same problem when running the query in SQL*Plus? If so,
     it would be an Oracle problem.

     John L. Kellogg
     MITI Technical Support Manager

______________________________ Reply Separator _________________________________
Subject: Leap Year Problem?
Author:  usa.net!SQR-USERS@netcomsv.netcom.com at Internet
Date:    03/04/96 7:25 AM


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