How Can I Calculate Age from a Date of Birth Using SQL?

Use DATE and GETDATE() functions in SQL to calculate age

In database tables containing personal information, it is often useful to determine the age of an individual. This requires converting the provided date of birth (DOB) into a usable format and calculating the elapsed years.

In order to convert a DOB string into a recognized date format, we can use the CAST() function:

SELECT CAST(DOB AS DATE) FROM YourTable;

Once the DOB is in the desired date format, we can use the GETDATE() function to retrieve the current date and time:

SELECT GETDATE();

Now we can calculate age by subtracting the DOB from the current date and dividing the result by the number of milliseconds in the year:

SELECT DATEDIFF(YEAR, DOB, GETDATE()) AS Age

Alternatively, if you prefer greater precision, you can use the following formula:

SELECT ROUND((JULIANDAY(GETDATE()) - JULIANDAY(DOB)) / 365.25, 2) AS AgeDecimals

This formula takes into account leap years and provides the age in decimal years.

To get the full picture, we can add age information to the existing table data:

SELECT ID, Name, DATEDIFF(YEAR, DOB, GETDATE()) AS Age
FROM YourTable;

The output will contain age values ​​along with other personal information, providing a more complete record for each individual.

The above is the detailed content of How Can I Calculate Age from a Date of Birth Using SQL?. For more information, please follow other related articles on the PHP Chinese website!