Re: Birthday Query

From: Ed Prochak <edprochak_at_magicinterface.com>
Date: Wed, 24 Oct 2001 05:16:52 GMT
Message-ID: <3BD679CC.3A1302AB@magicinterface.com>

Brian Tkatch wrote:
>
> 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

The problem is we are all still thinking in terms of dates. THe problem is to find them within so many days. So, yes Brian, my first reply was rather dumb. But using the day of the year may lead to the answer.

approach:
* convert date to DDD day of year.
Part A:
 for today's date >15 or <350 (avoiding both ends of the year problems for a moment)
 then this condition can be handled by:

select ...

where to_number(to_char(NVL(birthday,sysdate-20),'DDD')
       between to_number(to_char(sysdate,'DDD'))-15
          and  to_number(to_char(sysdate,'DDD'))+15

Part B:
 if today's date is 15 or less, we need part of last year which  can be covered by:

select ...
where to_number(to_char(NVL(birthday,sysdate-20),'DDD')

      between 1 
          and  to_number(to_char(sysdate,'DDD'))+15

union
select ...
where to_number(to_char(NVL(birthday,sysdate-20),'DDD')

       between to_number(to_char(sysdate-15,'DDD')) --- will be smaller than 366

          and 366

Part C:
 if today's date is 350 or more, we need part of next year which  can be covered by:

select ...
where to_number(to_char(NVL(birthday,sysdate-20),'DDD') --- will be smaller than 366

       between to_number(to_char(sysdate-15,'DDD'))
          and  366

union
select ...
where to_number(to_char(NVL(birthday,sysdate-20),'DDD')

      between 1 
          and  to_number(to_char(sysdate,'DDD'))+15

And since Part B and part C are differenc views of the same code, that can be simplified. With some time and thought, we could reduce it to a modulo arithmetic solution and maybe get it simplified even more.

The key is to realize that in a leap year, 12/31 becomes day 366. It doesn't matter whether this is a leap year or not. But looking at it, birthdays in leapyears may still be a problem. Let me think on it, but I'm sure this is the way to a solution. (it's just a matter of adjusting the birthdate)

HTH    Ed Prochak Received on Wed Oct 24 2001 - 00:16:52 CDT