Skip to main content

Reply to comment

Calculating Age as of a Date: How Leap Year Complicates Things

In:
16Jun2009

I am currently working on an application that must take a user's birthdate, and the date of a particular event, and use them to calculate the user's age when the event occurred.

MySQL has a built in function called DATEDIFF() that returns the number of days between any two dates. So to calculate the user's age when the event occured, we just use DATEDIFF() to find the number of days between event_date, and user_birthdate, and then divide by 365 and throw out the remainder, right?

Well, thats what I thought anyway. But in testing, it became obvious that if the event month/day was only a few days before the user's birthdate month/day, the answer was always one year more than it should have been.

Enter leap year. MySQL is smart enough to factor in leap years, so some years have 366 days between the same two calendar days. So to calculate age correctly, just divide by 365.25 instead of 365!

FLOOR(DATEDIFF(event_date,user_birthdate)/365.25) AS user_age_at_event_calc

Sometimes it is the simplest things that are the hardest!

Reply

Warning
I strongly encourage and welcome links and feedback. However, this site is moderated and comments with inappropriate links are rejected. Please do not post a one-line "Me too" or "Great post!" comment just so you can link to your site. Thank you for your understanding.
The content of this field is kept private and will not be shown publicly. If you have a Gravatar account associated with the e-mail address you provide, it will be used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • Lines and paragraphs break automatically.
  • Use to create page breaks.

More information about formatting options