CODEWITHSUNDEEP
sqlsql-servert-sql
mohan111
mohan111

Reputation: 8865

How to get Exact Age basing on the Dates

Sample Data : 

DECLARE @T Table (ID INT,Name VARCHAR(10),DOB DATE)
INSERT INTO @T (ID,Name,DOB) VALUES (1,'Mohan','1937-12-30')
INSERT INTO @T (ID,Name,DOB) VALUES (2,'Raj','1937-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')

My Query : 

Select 
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'   as Age from @T

This will give result in Years, months and Days . But My question how to get data only who are reaching 79 years of age in Coming 45 days . I'm struck can you please suggest me

Upvotes: 3

Views: 123

Answers (5)

Mansoor
Mansoor

Reputation: 4192

Try This : ;

WITH _CTE(_Id,_ConDate ,_Month , _Date ,Name , DOB,_DOBMonth , _DOBDate) AS
(SELECT ID , DATEADD(DAY,45,GETDATE()),DATEPART(M,DATEADD(DAY,45,GETDATE())),
DATEPART(DAY,DATEADD(DAY,45,GETDATE())),Name,DOB,DATEPART(MONTH,DOB),DATEPART(DAY,DOB)FROM @T WHERE (DATEDIFF(m, DOB, GETDATE())/12) = 78 )SELECT _Id Id,Name , DOB FROM _CTE WHERE _Month = _DOBMonth AND _Date = _DOBDate

Upvotes: 0

User
User

Reputation: 804

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

People who are reaching 79 years of age in the next 45 days:

SELECT * from @T where
DOB > DATEADD(year,-79,GETDATE()) and
DOB < DATEADD(year,-79,DATEADD(day,45,GETDATE()))

(Adjust for < vs <= and possible employ additional DATEADD/DATEDIFFs if you wish to round GETDATE() down to midnight, to suit your exact requirements)

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

What about this?

...
WHERE  (DATEDIFF(m, DOB, GETDATE())/12) = 78 AND 
       (DATEDIFF(m, DOB, GETDATE())%12) = 11 AND  
       (DATEDIFF(D, DOB, GETDATE())%12) < 15

Upvotes: 0

gotqn
gotqn

Reputation: 43626

You can do this:

Select 
       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
WHERE DATEDIFF(m, DOB, GETDATE())/12 < 79 -- we need only people how are not already 79 years old
    AND DATEDIFF(m, DATEADD(DAY,-45, DOB), GETDATE())/12 >= 79 -- we are making the DOB 45 days samller

Upvotes: 1

Related Questions