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:

<code class="language-sql">CONVERT(datetime, DOB)</code>

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:

<code class="language-sql">DATEDIFF(hour, CONVERT(datetime, DOB), getDate())</code>

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

<code class="language-sql">DATEDIFF(hour, CONVERT(datetime, DOB), getDate()) / 8766.0</code>

Output:

<code class="language-sql">SELECT ID, Name, DATEDIFF(hour, CONVERT(datetime, DOB), getDate()) / 8766.0 AS AGE, DOB
FROM [YourTable];</code>

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:

<code class="language-sql">(CONVERT(int, CONVERT(char(8), @Now, 112)) - CONVERT(char(8), @Dob, 112)) / 10000</code>

The best way to calculate age as a decimal:

<code class="language-sql">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</code>

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!