Re: Leap Year Problem?

[Nathan Treadway <nathant@ONTKO.COM>]

>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)))
[...]

The comments on the list about using an explicit
to_date($end_date||'23:59:59'...) for date comparisions are true,
but I don't think they completely explain the stated problem.

There are three date ranges mentioned in the message:
  A    01-FEB-96 to 29-FEB-96
  B1   01-FEB-96 to 28-FEB-96
  B2   29-FEB-96 to 29-FEB-96


Without the explicit to_date calls, range A would return *at least as
many* records as B1 and B2 together.  This is because times between
28-FEB-96 00:00:01 and 28-FEB-96 23:59:59 would not be returned for either
B1 or B2, but would be for A.  All other records would be treated the same
by A and (B1 and B2) (e.g. the records from 29-FEB-96 that are being skipped
because of the missing to_date will be skipped in both cases).

On the other hand, the message states that A is returning *fewer* records than
B1 and B2 together.


1 WHERE ((j1.effdt between $start_date and $end_date)
2    or ((j1.action_dt between $start_date and $end_date)
3    and (j1.effdt between $end_dt_minus_2yrs and $end_date)))


I can't say for sure without knowing what happens for other date ranges, but
my guess is that the records missing from A but found in B2 have '29-FEB-96
00:00:00' in action_dt (i.e. have times already truncated) but some other
value in effdt (probably 29-FEB-96 with some time value). If this is true,
then line 1 would reject and line 2 would match all those records in both
A and B2; the difference between the results would possibly due to the
calculation of $end_dt_minus_2yrs for A.

Mr. Douglas, perhaps you could let us know how this is calculated, as well
as results you get from other pairs of date ranges.  (Also, are all the
records missing from A found in B2, or are there some also found in B1?  Are
there any records in B2 that *are* found in A?)

If you've already solved the problem, what was causing it?

                                                Nathan
----------------------------------------------------------------------------
Nathan Treadway    | Ray Ontko & Co. | info@ontko.com (auto-reply server)
nathant@ontko.com  | Richmond, IN    | ftp.ontko.com, http://www.ontko.com/