How to Calculate Age from Date of Birth Using SQL's getDate()?

Use SQL’s getDate() function to calculate age based on date of birth

Question:

Many databases maintain personal information, including date of birth. In order to accurately track age for records management, the stored date of birth needs to be converted to the individual's age in years.

Solution:

Convert string date to date data type:

1. Use the CONVERT() function to convert the date of birth of varchar type to datetime data type:

CONVERT(datetime, DOB)

Calculate age in years:

2. Use the DATEDIFF() function to calculate the hour difference between the current date and time (obtained using getDate()) and the converted date of birth:

DATEDIFF(hour, CONVERT(datetime, DOB), getDate())

3. Divide the hour difference by 8766 (the number of hours in a year) to get age in decimal years:

DATEDIFF(hour, CONVERT(datetime, DOB), getDate()) / 8766.0

Output:

SELECT ID, Name, DATEDIFF(hour, CONVERT(datetime, DOB), getDate()) / 8766.0 AS AGE, DOB
FROM [YourTable];

Accuracy considerations:

Leap years and precise date calculations will affect accuracy. To improve accuracy, consider the following:

The best way to calculate age in whole numbers:

(CONVERT(int, CONVERT(char(8), @Now, 112)) - CONVERT(char(8), @Dob, 112)) / 10000

The best way to calculate age as a decimal:

1.0 * DATEADD(yy, @Dob, @Now)
+ CASE
    WHEN @Now >= DATEFROMPARTS(YEAR(@Now), MONTH(@Dob), DAY(@Dob)) THEN   -- 当年生日已过
        (1.0
        * DATEDIFF(day, DATEFROMPARTS(YEAR(@Now), MONTH(@Dob), DAY(@Dob)), @Now)
        / DATEDIFF(day, DATEFROMPARTS(YEAR(@Now), 1, 1), DATEFROMPARTS(YEAR(@Now) + 1, 1, 1))
        )
    ELSE    -- 当年生日未过
        -1 * (
            -1.0
            * DATEDIFF(day, DATEFROMPARTS(YEAR(@Now), MONTH(@Dob), DAY(@Dob)), @Now)
            / DATEDIFF(day, DATEFROMPARTS(YEAR(@Now), 1, 1), DATEFROMPARTS(YEAR(@Now) + 1, 1, 1))
        )
END

The above is the detailed content of How to Calculate Age from Date of Birth Using SQL's getDate()?. For more information, please follow other related articles on the PHP Chinese website!