Re: Birthday Query

Brian Tkatch wrote:
>
> I need to SELECT (people with) birthdays within a 15 day range of the
> time the query is run.
>
> I finally came up with a query that takes into account February 29th
> and year wraps (which is when the query is run in the end of December
> and the birthday is in January).
>
> I just wanted to post it here for comments. That is, is there an
> easier way to do this, and is this query accurate.
>
> To create a sample table:
>
> CREATE Table Test(Id NUMBER PRIMARY KEY, Birthday DATE UNIQUE);
>
> To populate the table:
>
> BEGIN
> INSERT INTO Test VALUES (1, NULL);
> FOR Day IN 2444240..2448257 LOOP
> INSERT INTO Test VALUES (Day - 2444238, TO_DATE(Day, 'J'));
> END LOOP;
> END;
> /
>
> The query:
>
> SELECT * FROM Test
> WHERE
> (
> ADD_MONTHS(Birthday, MONTHS_BETWEEN(TRUNC(SYSDATE,'YYYY'),
> TRUNC(Birthday, 'YYYY'))) BETWEEN (SYSDate - 15) AND (SYSDate + 15)
> OR
> ADD_MONTHS(Birthday, MONTHS_BETWEEN(TRUNC(SYSDATE,'YYYY'),
> TRUNC(Birthday, 'YYYY')) + 12) BETWEEN (SYSDate - 15) AND (SYSDate +
> 15)
> );
>
> I originally was working with:
>
> SELECT * FROM Test WHERE
> (
> TO_DATE(TO_CHAR(Birthday, 'MM/DD'), 'MM/DD') BETWEEN (SYSDate - 15)
> AND (SYSDate + 15)
> OR
> TO_DATE(TO_CHAR(Birthday, 'MM/DD') || '/' || (TO_CHAR(SYSDate,
> 'YYYY') + 1), 'MM/DD/YYYY') BETWEEN (SYSDate - 15) AND (SYSDate + 15)
> )
> AND Birthday IS NOT NULL;
>
> It did not handle a birthday on the 29. I tried using
> TO_DATE(Birthday, 'DDD'), so 2/29 would become 3/1, but then 3/31 in a
> leap year was now invalid in a non-leap year.
>
> Further, the "Birthday IS NOT NULL" clause is required at the end of
> the query. If put before the other clause, the NULL caused the second
> TO_DATE() to complain. Are WHERE clauses also traveled backwards?
>
> Brian

Assuming Birthday is a DATE type field, why didn't you try this?

select * from test
where birthday between sysdate-15 and sysdate+15;

Handles leapyears and everything fine. ORACLE treats dates as units of days for numberical calculations. You don't even need the "and birthday IS NOT NULL".
Try it.

At worst you may want to do a truncate() on the dates to make them all relative to the same time of day. I'll leave that as an exercise for the reader.

And yes the WHERE clause is effectively precessed bottom up.

Enjoy. Received on Sun Oct 21 2001 - 20:59:44 CDT