I have a table of users with various dates like birthday, date of hire, and annual review date. The birthday may or may not have a year, the date of hire does have a year, and the review date does not have a year.
I want to be return a query across a date range that returns the rows with the dates calculated for the year of the date range for these recurring events.
For example: A user with the following:
dob:1980-05-05
doh:2005-06-22
review:0000-10-01
Then if I query for a range of say, 2012-05-01 to 2013-12-01 I'd like it to return records like:
Event,Date
dob,2012-05-05
doh,2012-06-22
review,2012-10-01
dob,2013-05-05
doh,2013-06-22
review:2013-10-01
I realize that this probably will be done in separate UNION queries on each date field which is fine. This isn't exactly the same problem as finding dates x days in the future as I need the recurring date with the proper year in the given date range which may be over multiple years if the range is large enough.
I can only think of doing this programatically. First checking if the start and end range span over a year. Then split the dates into start date to end of year, start of year to end date etc. and run a query for each span.