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




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:

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
Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.