View Full Version : How to handle dates when year isn't always known ..

02-23-2008, 03:52 PM

This post is partially related to storing something in a database, but also concerns PHP, so that's the reason I'm posting it in this forum.

I'm currently building a website which contains a birthday calendar and I'm wondering what the best way is to store the dates of the birthdays. The first answer that came to my mind is to use MySQL's date-field. The only thing is that in some cases the birthyear is left empty (simply because somebody that adds a person to his calendar, doesn't know the year of birth ..)

A second option I was thinking about is to use 3 columns (day, month, year).

The only problem is that I easily want to be able to list the upcoming birthdays for the next 7 days, even if it exceeds a month. Then this approach isn't that practical, I guess.

I'd appreciate it if somebody can share his advice on this!

Cheers!- Michiel

02-23-2008, 06:53 PM
Store the date as a "date" data type in MySQL. This will give you all the flexibility you need-- you can format the date any way you like, you can perform comparisons on the date, you can perform addition and subtraction on the date, and yes, you can find all the birthdays coming up in the next 7 days.

The reason you gave for not using a MySQL date column is resolved by making the column NULLable and then simply insert a null into the column if the birthday is not known.

02-23-2008, 10:14 PM
Ok .. I'm using the date data type now .. but haven't figured out how to select the upcoming birthdays, in a way that I can go beyond the months. So when you're close to the end of a month, I'd want to select the birthdays of the first days of the next month as well.

I'm currently using DAYOFMONTH(birthday) and MONTH(birthday) to do the selecting. Off course I can come up with a query, using these functions and still span over months, but it seems like there should be a simpler solution ..

Thanks so far! -Michiel

02-24-2008, 04:44 AM
This should be helpful.




02-25-2008, 12:52 AM
There is a solution, but it's kind of tricky because you have to ignore the "year" part of the birthday. Normal date comparison, such as finding all account that expire in the next month, is easy to accomplish with the date_add() MySQL function.

Anyways here is an example of how I did it (there may be a simpler way):

//The WHERE clause assembles each birthday using the current year
//(or next year if the birthday has already passed for the year),
//then qualifies that date if it falls between current date and 1 week from now.

SELECT first_nm,
FROM people_tbl
WHERE if (
concat(year(now()),'-',date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')) >= curdate(),

concat(year(now()), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')),

concat(year(now() + interval 1 year), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')))

between date(now()) and date((now()) + interval 1 week)