Re: Birthday Query

On Tue, 23 Oct 2001 18:09:35 GMT, "Scott Mattes" <ScottMattes_at_yahoo.com> wrote:

>Brian,
>Here is my thought process: take a birthday, say 10/23/45, and convert it to
>YYYYDDD and lop off the YYYY (to_char( birthday, 'DDD') ) and then using the
>wonders of assumption that are built into Oracle convert it to a date value
>with today's year (to_date( DDD_var, 'DDD') ). Now, if the input were
>12/31/2000 this would die, because the DDD_var would be 366 and 2001 isn't a
>leap year.
>
>The 'only' problem I see is with the 12/31 of a leap year being converted to
>366 and not fitting into a a non-leap year.
>
>IF this is the case, that this is the ONLY problem, then in your case if you
>subtracted 1 from the birthday BEFORE converting it to_char and then add 1
>after converting it to_date you would end up with 12/31/01. People with
>birthdays of 2/29 would show up as 3/1, but everyone else should be fine.
>
>What do you think?

Subtracting 1 would die on 1/1 and adding one would die on 12/31. I'd have to check the dates before doing *any* conversions.

Further, if I subtracted in a non-leap year and then added in a leap year, 3/1 would become 2/28 and then 2/29, throwing the equation off by a day.

So far, the only function I found to handle 2/29 without messing things up is ADD_MONTHS(). Together with MONTHS_BETWEEN() I can bring and old date to this year. IMHO, ADD_YEARS() would be so much nicer, however.

Brian Received on Tue Oct 23 2001 - 13:40:59 CDT