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.
"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in
message news:3bcf35d0.621013687_at_news.alt.net...
> 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 Mon Oct 22 2001 - 17:30:22 CDT
