Calculating Age from Date of Birth in MySQL

I was doing some experiments the other day with MySQL and wrote some interesting queries to calculate age using MySQL. Here you go.

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),”1978-03-28″)), ‘%Y’)+0 AS age

1:- DATEDIFF(NOW(),”1978-03-28″)
      This function DATEDIFF() returns difference of two dates in days, e.g. DATEDIFF(“1978-04-28”, “1978-03-28”) will return 31 days. So by using NOW() i.e. current date, in the above query, we get, say, 10744 days.

2:- FROM_DAYS(10744)
      retuns the date starting from 0000-00-00 i.e. since year 0… so this function outputs “0029-06-01″, i.e. the difference between two dates “1978-03-28” & “2008-08-27” is precisely 29 years, 6 months & 1 day. We need just years, so we use

3:- DATE_FORMAT(“0029-06-01”, %Y) +0
      and it returns us 29 as an integer value.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(“1978-03-28”)), ‘%Y’)+0 AS age

1:- TO_DAYS(“1978-03-28”)
      The only new function in this query is TO_DAYS(date), which converts our date to number of days starting from year 0 i.e. the opposite of function FROM_DAYS(days). So this gives us 722536 days. The other function TO_DAYS(NOW()) returns us 733280 days (for 2007-08-27). Subtracting the two, we get 733280 – 722536 = 10744 days. From here on, we move on to step 2 in the above scenario.


SELECT EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),”1978-03-28″))))+0 AS age

1:- EXTRACT(YEAR FROM “0029-06-01”) +0
     Taking the queue from step 2 in the first scenario, FROM_DAYS(10744) we apply another function. EXTRACT(date) which extracts a part from the given date as per the format, here YEAR, and it returns us 29 as an integer value.

This last query using EXTRACT() method is a bit slower than the one using DATE_FORMAT() – (.0001 sec). All these queries return the age in years (as a numeric value).

Powered by ScribeFire.