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:

<code class="language-sql">SELECT CAST(DOB AS DATE) FROM YourTable;</code>

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

<code class="language-sql">SELECT GETDATE();</code>

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:

<code class="language-sql">SELECT DATEDIFF(YEAR, DOB, GETDATE()) AS Age</code>

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

<code class="language-sql">SELECT ROUND((JULIANDAY(GETDATE()) - JULIANDAY(DOB)) / 365.25, 2) AS AgeDecimals</code>

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:

<code class="language-sql">SELECT ID, Name, DATEDIFF(YEAR, DOB, GETDATE()) AS Age
FROM YourTable;</code>

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!