How to get Age calculation for different years

Question

I have some sample data like this :

DECLARE @T Table (ID INT, Name VARCHAR(10), DOB DATE)
INSERT INTO @T (ID, Name, DOB) VALUES (1,'Mohan', '1937-12-01')
INSERT INTO @T (ID, Name, DOB) VALUES (2,'Raj',   '1936-12-25')
INSERT INTO @T (ID, Name, DOB) VALUES (5,'Manny', '1937-01-30')
INSERT INTO @T (ID, Name, DOB) VALUES (3,'kamal', '1938-12-12')
INSERT INTO @T (ID, Name, DOB) VALUES (4,'Raj',   '1937-05-12')

SELECT * FROM  @T

By using this below query i will find the age in years and months and Days

SELECT Name,cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) + ' Y & ' + 
   cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) + ' M & ' +
   cast((DATEDIFF(D, DOB, GETDATE())%12) as varchar) + ' d'  from @T

I just want to know who is reaching the 78,79 and 80 years in coming 45 days .

By using below query i can get the the data who is reaching 80 years in coming 45 days

       where
DOB > DATEADD(year,-80,GETDATE()) and
DOB < DATEADD(year,-80,DATEADD(day,45,GETDATE()))

this all giving data one at a time if i pass 80 it will give data who are reaching 80 years.If i give 79 it will give data who are reaching 79 years. But how can i all the data who are reaching 78,79 and 80 years in coming 45 days.

suggest me the best way

 Name       Age
    Mohan   78 Y & 11 M & 9 d
    Raj     79 Y & 11 M & 2 d
    kamal   77 Y & 11 M & 5 d

Above people are reaching respective years with in 45 days

Mohan 79 --> with in 45 days
Raj   80 --> with in 45 days
kamal 78 --> with in 45 days

What happen with people with exact age, for example exactly 79 years? Is it counted 0 days to 79 or 365 days to 80? Do you want those people to show in your result? — Niyoko, Nov 09 '16 at 07:14
No if the person reaching 79 with in 45 days they should come and people are who are reaching 80 in 45 days should come .not the person who is reaching 80 in next 3 or 6 months are 365 days @NiyokoYuliawan — mohan111, Nov 09 '16 at 07:16
@Mr.SuicideSheep yeah it's sample we can modify according to that — mohan111, Nov 09 '16 at 08:18

Damien_The_Unbeliever · Accepted Answer · 2016-11-09T10:45:25.847

To find everyone whose birthday is within the next 45 days, it's often easiest to reset all of the values you're comparing into a single year, and then work from there:

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','1937-12-01')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','1936-12-25')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Manny','1937-01-30')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'kamal','1938-12-12')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'Raj','1937-05-12')

declare @Today date
--Set "Today" to today's date in 2000
set @Today = DATEADD(year,DATEDIFF(year,GETDATE(),'20000101'),GETDATE())
declare @Plus45 date
set @Plus45 = DATEADD(day,45,@Today)
--And reset to 2000 (year wrap-around)
set @Plus45 = DATEADD(year,DATEDIFF(year,@Plus45,'20000101'),@Plus45)
select @Today as Today,@Plus45 as Plus45

;With ResetDOBs as (
    select
        *,
        DATEADD(year,DATEDIFF(year,DOB,'20000101'),DOB) as DOB2000
    from
        @T
)
select
    *
from
    ResetDOBs
where
    DOB2000 between @Today and @Plus45 or
    (@Plus45 < @Today and
        (DOB2000 <= @Plus45 or DOB2000 >= @Today)
    )

Results:

ID          Name       DOB        DOB2000
----------- ---------- ---------- ----------
1           Mohan      1937-12-01 2000-12-01
3           kamal      1938-12-12 2000-12-12

We could use any year for the reset, provided that it's a leap year. I chose 2000. There's a small bit of juggling both in computing @Plus45 and in the final Where clause to deal with oddities with using this method when the 45 day period overlaps the year-end transition, December/January.

(In my time zone, it's currently 2016-11-09, and so Raj (ID 2) is not quite within the 45 day window yet)

This query:

SELECT Name,cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) + ' Y & ' + 
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) + ' M & ' +
cast((DATEDIFF(D, DOB, GETDATE())%12) as varchar) + ' d'  from @T

Is still badly broken. Raj (ID 2) would not report their age as 79 Y & 11 M & 2 d, as you report. They'd report their age as 79 years, 10 months and some days. Your month calculation can easily overcount by one, and your day calculation is confused (why %12 for a day calculation?).

Here's one way to do the Y/M/D calculations that should be closer to human expectations of people's ages. If you have a numbers table, you can use it to create the NumPairs CTE, rather than how I've created it here:

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','1937-12-01')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','1936-12-25')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Manny','1937-01-30')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'kamal','1938-12-12')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'Raj','1937-05-12')

declare @Today date
set @today = GETDATE()

;With NumPairs as (
    select 0 as n1, 1 as n2
    union all
    select n1+1,n2+1
    from NumPairs
    where n1 <= 150
)
select
    t.*,
    y.n1 as Y,
    m.n1 as M,
    d.n1 as D
from
    @T t
        cross apply
    (select n1,DATEADD(year,n1,DOB) as DOBy from NumPairs
    where DATEADD(year,n1,DOB) <= @today and
        DATEADD(year,n2,DOB) > @today
    ) y
        cross apply
    (select n1,DATEADD(month,n1,DOBy) as DOBmy from NumPairs
    where DATEADD(month,n1,DOBy) <= @today and
        DATEADD(month,n2,DOBy) > @today
    ) m
        cross apply
    (select n1 from NumPairs
    where DATEADD(day,n1,DOBmy) = @today
    ) d
option (maxrecursion 0)

Result:

ID          Name       DOB        Y           M           D
----------- ---------- ---------- ----------- ----------- -----------
3           kamal      1938-12-12 77          10          28
1           Mohan      1937-12-01 78          11          8
2           Raj        1936-12-25 79          10          15
5           Manny      1937-01-30 79          9           10
4           Raj        1937-05-12 79          5           28

@mohan111 - I'm shifting everything around so that all of the dates I'm working with have been put into the year 2000. Having done that, I can then perform simpler comparison operations — Damien_The_Unbeliever, Nov 09 '16 at 10:12
Why Raj ID is not coming into the below 80 years as he is in the bracket of 45 days — mohan111, Nov 09 '16 at 10:22
@mohan111 - as I point out towards the bottom of my answer, in my time zone currently, it's 2016-11-09. 45 days from today is 2016-12-24. Raj's birthday isn't until the 25th December, so does not appear to fit the requirement. And as pointed out below the line, you current Y/M/D calculations are plain wrong. So whilst it would seem that someone who is 79 years, 11 months and 2 days old would have a birthday within 45 days, that's not Raj's current age. — Damien_The_Unbeliever, Nov 09 '16 at 10:28
Your query simply checks if the month-date portion of date of birth matches the [today, +45 day] range, while OP wants to check if the person is turning 78, 79 or 80 in the next 45 days. Change Mohan's year of birth to 1900 or 1990 and it will be selected which is wrong. — Salman A, Nov 09 '16 at 10:37

score 1 · Answer 2 · edited May 23 '17 at 12:01

1

You can calculate the Next age and days to next birthday first, then filter by them.

WITH y AS
(
    SELECT *, 
    DATEDIFF(hour,DOB,GETDATE())/8766 + 1  NextAge,
    DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(hour,DOB,GETDATE())/8766 + 1, DOB)) ToNextBirthday
    FROM @T
)
SELECT * FROM y
WHERE
    y.NextAge IN (78, 79, 80)
    AND y.ToNextBirthday <= 45

Edit

Answer above will have issues in some edge cases that mentioned in the comment. Below I borrow code from this answer to calculate the NextAge. The rest of the logic is still the same.

DECLARE @today DATE;
SET @today = GETDATE();

WITH a AS
(
    SELECT *, 
    (CONVERT(int,CONVERT(char(8), @today, 112))-CONVERT(char(8), DOB, 112))/10000 + 1 NextAge
    FROM @T
), b AS
(
    SELECT *,
    DATEDIFF(DAY, @today, DATEADD(YEAR, a.NextAge, DOB)) ToNextBirthday
    FROM a
)

SELECT * FROM b
WHERE
    b.NextAge IN (78, 79, 80)
    AND b.ToNextBirthday <= 45

edited May 23 '17 at 12:01

Community

1
1

answered Nov 09 '16 at 07:31

Niyoko

7,512
4
32
59

Using 8766 is kindof a fudge and tries to accommodate leap years. You'll find it can give results which disagree with what a human might state. – Damien_The_Unbeliever Nov 09 '16 at 07:35
E.g. for someone with a DOB of `2016-01-05`, running this code after 6am on `2017-01-04` will report their next age as 2, not 1. – Damien_The_Unbeliever Nov 09 '16 at 07:41
@Damien_The_Unbeliever Yes, it's a bit complicated when facing leap years. I tried to make it as simple as possible that covers most cases. In some edge cases, it might be different with concept of "Age" that human use. – Niyoko Nov 09 '16 at 07:41
@NiyokoYuliawan i will implement in this my code let you know – mohan111 Nov 09 '16 at 08:59

Salman A · Answer 3 · 2016-11-09T10:40:02.537

I just want to know who is reaching the 78,79 and 80 years in coming 45 days.

There is an easier way to look at the problem. You have a date range:

[today, today + 45 days]

And you need to check if someone has a date of birth inside these ranges:

[today - 78 years, today + 45 days - 78 years]
[today - 79 years, today + 45 days - 79 years]
[today - 80 years, today + 45 days - 80 years]

The query simply becomes:

DECLARE @D0 AS DATE = CURRENT_TIMESTAMP                    -- 2016-11-09
DECLARE @D1 AS DATE = DATEADD(DAY, 45, CURRENT_TIMESTAMP)  -- 2016-12-24

SELECT *
FROM @T
WHERE DOB BETWEEN DATEADD(YEAR, -78, @D0) AND DATEADD(YEAR, -78, @D1)
OR    DOB BETWEEN DATEADD(YEAR, -79, @D0) AND DATEADD(YEAR, -79, @D1)
OR    DOB BETWEEN DATEADD(YEAR, -80, @D0) AND DATEADD(YEAR, -80, @D1)

score 0 · Answer 4 · answered Nov 09 '16 at 07:16

where
DOB > DATEADD(year,-80,GETDATE()) and
DOB < DATEADD(year,-80,DATEADD(day,45,GETDATE()))

if this works, you could add some 'or clauses' to achieve what you're looking for (if I got it right):

where
(DOB > DATEADD(year,-80,GETDATE()) and
DOB < DATEADD(year,-80,DATEADD(day,45,GETDATE())))
or
(DOB > DATEADD(year,-79,GETDATE()) and
DOB < DATEADD(year,-79,DATEADD(day,45,GETDATE())))
...

score 0 · Answer 5 · answered Nov 09 '16 at 07:41

SELECT * from @T 
WHERE 
        (DATEDIFF(dd,GETDATE(),DATEADD(yy,78,dob))<45
            AND 
         DATEDIFF(dd,GETDATE(),DATEADD(yy,78,dob))>0)
OR
        (DATEDIFF(dd,GETDATE(),DATEADD(yy,79,dob))<45
            AND 
         DATEDIFF(dd,GETDATE(),DATEADD(yy,79,dob))>0)
OR
        (DATEDIFF(dd,GETDATE(),DATEADD(yy,80,dob))<45
            AND 
         DATEDIFF(dd,GETDATE(),DATEADD(yy,80,dob))>0)

score 0 · Answer 6 · answered Nov 09 '16 at 08:32

Try

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','1937-12-01')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','1936-12-25')
INSERT INTO @T (ID,Name,DOB) VALUES (5,'Manny','1937-01-30')
INSERT INTO @T (ID,Name,DOB) VALUES (3,'kamal','1938-12-12')
INSERT INTO @T (ID,Name,DOB) VALUES (4,'Raj','1937-05-12')

declare @dd date = dateadd(day,45,getdate());
declare @dmd int = month(@dd)*100+day(@dd);
select @dd, @dmd;
select *
from  @T
where datediff(year, DOB, @dd) - case when month(DOB)*100+day(DOB) > @dmd then 1 else 0 end in (78,79,80)
order by DOB;

It is not giving any expected result – mohan111 Nov 09 '16 at 09:38 — mohan111, Nov 09 '16 at 09:38

score 0 · Answer 7 · answered Nov 09 '16 at 10:42

I had once reated a SQL function for calculating age, I hope it helps you for your requirement too.

But your question has an additional aspect, you want to see 45 days ahead

So instead of calculating age for current date, we require to add 45 days to now and then calculate age in SQL

Here is how I did it

DECLARE @T Table (ID INT, Name VARCHAR(10), DOB DATE)
INSERT INTO @T (ID, Name, DOB) VALUES (1,'Mohan', '1937-12-01')
INSERT INTO @T (ID, Name, DOB) VALUES (2,'Raj',   '1936-12-25')
INSERT INTO @T (ID, Name, DOB) VALUES (5,'Manny', '1937-01-30')
INSERT INTO @T (ID, Name, DOB) VALUES (3,'kamal', '1938-12-12')
INSERT INTO @T (ID, Name, DOB) VALUES (4,'Raj',   '1937-05-12')
INSERT INTO @T (ID, Name, DOB) VALUES (3,'kodyaz', '2015-12-25')
INSERT INTO @T (ID, Name, DOB) VALUES (3,'eralper', '2015-12-24')

SELECT 
*, 
dbo.fn_CalculateAge(DOB,dateadd(dd,45,getdate())) fo
FROM  @T
--where
--dbo.fn_CalculateAge(DOB,dateadd(dd,45,getdate())) = 79 and
--dbo.fn_CalculateAge(DOB,getdate()) = 78

Please note that if you uncomment the WHERE clause you will get the people who are 78 now and will be 79 in 45 days period

I hope it helps,

How to get Age calculation for different years

7 Answers7

Edit