.Net STUFF's - Guide to get it..

Calculate age using MySQL

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 y