Generate random birth date in MySQL table and get Age by select

Today I was solving generating of random data for testing and manipulation with the date of the birth.

Here are two simple SQL queries solving both:

Query for generate random date of birth in whole table

UPDATE

people

SET

date_of_birth=CONCAT_WS('-',(FLOOR( 1900 + RAND( ) *100)),(FLOOR( 1 + RAND( ) *12 )),(FLOOR( 1 + RAND( ) *28 )))

Query for selecting date of birth and calculation of age in MySQL:

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

You may also like