Birthday Query

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 Received on Thu Oct 18 2001 - 15:16:36 CDT