Re: Birthday Query

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?

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news:3bd4c71d.985890281_at_news.alt.net...
> On Mon, 22 Oct 2001 22:30:22 GMT, "Scott Mattes"
> <ScottMattes_at_yahoo.com> wrote:
>
> >Have you tried a solution where you take and format the birthday to_char
as
> >DDD and then to_date from DDD? It will handle leap years, except for
12/31.
> >And if you always subtract/add 1 from/to birthday and sysdate that should
> >take care of that one also.
>
> I tried that, but then dropped it because of 12/31 problem. How can I
> add or drop a day? Do you mean with a DECODE operation checking when
> the day is 31? If you mean 2/28, then it dies anyway when
> adding/subtracting on 2/28 or 3/1 in a leap year, and I'll have the
> same poblem. If you mean to DDD then I'll have a problem when I hit 0
> or 366/7 Of course I could MOD it based on the amount of days in this
> year, but that seems a bit tedious, and would be off by two days when
> 367 is modded.
>
> I'd rather not be off a day, unless I have to, as with 2/29, in which
> it is my choice to make it 2/28 or 3/1. And I let the function I use
> choose that. :-)
>
> I appreciate the reply. Please, let me know what you think.
>
> Brian
Received on Tue Oct 23 2001 - 13:09:35 CDT