mysql date function

I have a problem to come up with que开发者_运维技巧ry. Scenario is: To produce a list showing the name and age(in years) of child on the day that the query is run. The list should be stored in descending order of age(i.e. oldest children first) with children of the same age listed alphabetically by surname, first name. TABLE STRUCTURE:

CHILD TABLE ( child_id[Primary Key], child_fame, child_sname, child_gender, child_dob)

1. child_id_1=07/02/1999

2. child_id_2=13/11/2002

3. child-id_3=13/11/2002

4. child_id_4=26/10/1999

5. child_id_5=04/05/2004 etc...

This should do it:

SELECT *, TIMESTAMPDIFF(YEAR, child_dob, CURRENT_TIMESTAMP) AS age 
FROM child
ORDER BY child_dob, child_sname, child_fname;

If what you need is to calculate the age of the child from the date of birth you can take a look at this link: http://ma.tt/2003/12/calculate-age-in-mysql/ there are examples and other ways in the comments.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age