Fwd: Date math

On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich <adam(dot)r(at)sbcglobal(dot)net> wrote:

> Hello,
> I have a table with a DATE field "birth_date". The data obviously contains
> various dates in the past, such as 07/04/1970. In my query, I need to
> retrieve the person's "next" birthday. In other words, for the example date
> 07/04/1970, the query should return 07/04/2009 for the current week, but
> after this July 4th, it would return 07/04/2010. Ultimately, I need to find
> people with "next" birthdays within a certain range.
>
> The best I've come up with so far is:
>
> select case
> when to_char(birth_date, 'MMDD') <= to_char(current_date, 'MMDD') then
> (to_char(birth_date, 'MM/DD/')||date_part('year', now())+1)::date
> else (to_char(birth_date, 'MM/DD/')||to_char(current_date,'YYYY'))::date
> end as next_birthday
> from people inner join openings on people.id=openings.id
> where case
> when to_char(birth_date, 'MMDD') <= to_char(current_date, 'MMDD') then
> (to_char(birth_date, 'MM/DD/')||date_part('year', now())+1)::date
> else (to_char(birth_date, 'MM/DD/')||to_char(current_date,'YYYY'))::date
> end between openings.item_date - interval '1 month'
> and openings.item_date + interval '1 month'
>
> This seems to work for most cases, but fails for Feb 29 birthdates. And
> converting dates to strings and back again seems like a hack... Is there a
> better way? (I prefer to treat 02/29 as 03/01 for non-leap years)
>
> Is there a way to add just enough years to birth_date to bring the result
> into the future?
>
> Adam

You could use the extract() function to calculate the day of year of the
person's birthdate and then check if this number is within today's day of
year and range of days you want to check for, for example, today's day of
year + 30 days to be within a month. That way you don't need to worry about
years at all. You may need to double check this will work on the leap years
though!