Calculating Age Using DoB from a Specific Date in the past

Question

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.

https://stackoverflow.com/questions/6611103/calculate-age-of-a-person-in-sql/6611361 — shawnt00, Mar 30 '21 at 18:19
Um... someone born on 1991-02-18 would be not-yet 1 year old on 1992-02-01 (turns 1 on the 18th). So, I think, they would have been 28, not 29, on 2020-02-01. — Brian Stork, Mar 30 '21 at 18:25

score 1 · Accepted Answer · answered Mar 30 '21 at 18:21

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.

Amazing. Thank you so much for your time. I love the logic and explanation! — Gregory Brauninger, Mar 30 '21 at 18:39

score 1 · Answer 2 · answered Mar 30 '21 at 18:33

Convert the dates to 8-character integers, subtract, and divide by 10000:

declare @Birthdate date = '2/18/91'
declare @AsOfDate  date = '2/1/2020'
select (0+convert(char(8),getdate(),112) - convert(char(8),@birthdate,112)) / 10000 as CurrentAge
     , (0+convert(char(8),@asofdate,112) - convert(char(8),@birthdate,112)) / 10000 as FebAge

score 0 · Answer 3 · answered Mar 30 '21 at 18:21

0

datediff(year, <birthdate>, <currentdate>) +
case when
      month(<birthdate>) > month(<currentdate>) or
      month(<birthdate>) = month(<currentdate>) and
      day(<birthdate>) > day(<currentdate>)
    then -1 else 0 end

answered Mar 30 '21 at 18:21

shawnt00

16,443
3
17
22

Calculating Age Using DoB from a Specific Date in the past

3 Answers3