How to Accurately Calculate Age in SQL Server Using Date of Birth?

Precise Age Calculation in SQL Server using Date of Birth

SQL Server offers efficient methods for calculating age from a date of birth. The CAST() function readily converts date-of-birth strings into the DATE/DATETIME data type, enabling various date computations, including age determination.

Consider this sample data:

ID	Name	DOB
1	John	1992-01-09 00:00:00
2	Sally	1959-05-20 00:00:00

Age Calculation with DATEDIFF()

The DATEDIFF() function calculates the difference between two dates. Combining it with GETDATE() (which provides the current date and time), we can determine the age in various units:

<code class="language-sql">SELECT ID, Name, DATEDIFF(hour, DOB, GETDATE()) AS AgeInHours,
       CONVERT(int, ROUND(DATEDIFF(day, DOB, GETDATE()) / 365.25, 0)) AS AgeYearsApprox
FROM table_name;</code>

This yields approximate age in years, accounting for the average number of days in a year (365.25). A more precise method is needed for accurate results.

Enhanced Age Calculation Methods

For greater accuracy, especially when considering leap years, more sophisticated approaches are recommended.

Integer Age:

This method provides the age in whole years:

<code class="language-sql">SELECT ID, Name,
       (YEAR(GETDATE()) - YEAR(DOB)) - CASE WHEN MONTH(GETDATE()) < MONTH(DOB) OR (MONTH(GETDATE()) = MONTH(DOB) AND DAY(GETDATE()) < DAY(DOB)) THEN 1 ELSE 0 END AS AgeYearsInt
FROM table_name;</code>

Decimal Age (More Precise):

This calculation offers a more precise age, including fractional years:

<code class="language-sql">SELECT ID, Name,
       DATEDIFF(year, DOB, GETDATE()) +
       CASE
           WHEN DATEADD(year, DATEDIFF(year, DOB, GETDATE()), DOB) > GETDATE() THEN -1
           ELSE 0
       END AS AgeYearsDecimal
FROM table_name;</code>

These improved methods provide more accurate age calculations, handling the complexities of leap years and day-of-year differences for more reliable results. Choose the method that best suits your precision requirements.

The above is the detailed content of How to Accurately Calculate Age in SQL Server Using Date of Birth?. For more information, please follow other related articles on the PHP Chinese website!