sqlsql-serverdatediffdatepartdate

Calculating Age Using DoB from a Specific Date in the past


I am looking for some help with this:

I need to calculate a members age (DoB field) as of 2/1/2020. For instance, if a member had a DoB of 2/18/91, I would want to see two fields. 1 for the actual age of the member as of today, and then the age of the member as of 2/1/2020. In this example the ages should 30 in the first field, but 29 in the second field.

I tried using some datefiff and datepart, but I can't seem to get it. I am using SSMS 2016.


Solution

  • Your calculations are wrong. That person should be 28 at that time.

    A simple trick is to treat the date as a number in the form of yyyyMMdd and then subtract the target date from DOB and get the integer part. With your sample dates:

    DECLARE @dob DATETIME='19910218';
    DECLARE @targets TABLE(target DATETIME);
    INSERT @targets(target)VALUES('20200201'), (GETDATE());
    SELECT
         FLOOR(((YEAR(target)* 10000+MONTH(target)* 100+DAY(target))
                -(YEAR(@dob)* 10000+MONTH(@dob)* 100+DAY(@dob))
               )/ 10000
              )
    FROM @targets;
    

    PS: Floor might be unnecessary, SQL server makes an integer division but I would like to keep it as a safety.