On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich <adam.r@xxxxxxxxxxxxx> 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!
