Wednesday, August 21, 2013

Age Calculation in MySQL based on Date of Birth Field

To find out age in mysql we can use two methods. (Assume that DOB is the field name in table)

1)SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(DOB, '00-%m-%d')) AS age FROM students

The above query returns the exact age. (looking for birth date)

- 2013-08-22 - 2008-11-02  returns 4 

2) SELECT YEAR( CURDATE( ) ) - YEAR( DOB ) AS age FROM students.

The above query returns the age difference by year (looking for year only)

- 2013-08-22 - 2008-11-02  returns 5

No comments: