mohan111 - 26 days ago 12
SQL Question

# How to get Age calculation for different years

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
``````

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
``````

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
*,
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
) y
cross apply
(select n1,DATEADD(month,n1,DOBy) as DOBmy from NumPairs
where DATEADD(month,n1,DOBy) <= @today and
) 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
``````