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