Re: Birthday Query

On Mon, 22 Oct 2001 01:59:44 GMT, Ed Prochak <edprochak_at_magicinterface.com> wrote:
>
>Assuming Birthday is a DATE type field, why didn't you try this?
>
>select * from test
>where birthday between sysdate-15 and sysdate+15;

Unless I need to know about newborns, or am clairvoyant, this won't work. :-)

Birthdays are given with a year. I need to know about their birthday *this* year.

>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.

The NOT NULL is required to handle the conversion when the date is NULL. Otherwise, when using TO_DATE it'll complain. But, yes, if I did not need to use TO_DATE, I could remove that clause.

>
>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.

I never even thought of that.

>
>And yes the WHERE clause is effectively precessed bottom up.
>
>Enjoy.

Brian Received on Mon Oct 22 2001 - 09:46:55 CDT