Re: Fwd: Date math

Guy Flaherty wrote:
> On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich <adam(dot)r(at)sbcglobal(dot)net
> <mailto: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.
>
>
>
> 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!
>
>

Thanks! that's even better than what I just came up with:

birth_date + ((interval '1 year') * ceil(EXTRACT(DAYS FROM (now() -
birth_date))/365.25))

And I like the "Day of year" solution because (I think) I can use a
functional index on that value.