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

Answer 1

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

Answer 2

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,

Answer 3

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)

Answer 4

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;

Answer 5

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

Answer 6

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)

Answer 7

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())))
...