How to use datediff to get age less than 12 months

Abraham Olatubosun

10y973SQL

Dear Partners in coding,

I want to appreciate all your support and tips you have been giving me so far.

I have this challenge i want to get all the age of my patients under 12 months, i tried using Datediff(mm,DOB,Getdate()) this did not give me the age when i compare with the documented hard copy.

please i will appreciate any fast solution to this issue.

thank you

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Esakki Muthu10y ago
Accepted answer
try this,
Create table Patients(PatientID Int,Name varchar(50),Location varchar(50))
Create table PatientsDOB(PatientID Int,DBO datetime)
insert into Patients values(1,'Arnold','Location 1')
insert into Patients values(2,'Bossman','Location 2')
insert into Patients values(3,'CityBoy','Location 3')
insert into Patients values(4,'Drekel','Location 4')
insert into Patients values(5,'Emrokin','Location 5')
insert into Patients values(6,'Frasher','Location 6')
insert into Patients values(7,'Golavendor','Location 7')
insert into PatientsDOB values(1,'2005-02-23')
insert into PatientsDOB values(2,'2009-08-2')
insert into PatientsDOB values(3,'2011-04-12')
insert into PatientsDOB values(4,'2015-08-27')
insert into PatientsDOB values(5,'2012-05-20')
insert into PatientsDOB values(6,'2008-09-15')
insert into PatientsDOB values(7,'2015-03-25')
SELECT p.PatientID,p.Name,p.Location,
CASE
WHEN DATEDIFF(Month, pd.DBO, GetDate()) <=60 THEN 'Below 5'
WHEN DATEDIFF(Month, pd.DBO, GetDate()) > 60 THEN 'Above 5' END AS Age
FROM Patients p
INNER JOIN PatientsDOB pd ON p.PatientID = pd.PatientID
Abraham Olatubosun10y ago
Many thanks to you guys suggestion worked well after modifying the script.
I appreciate you all
Abraham Olatubosun10y ago
Many thanks to Esakki Muthu and Manas Mohapatra the thing is, the Age is not the only data i wanted their is patient ID, Location, State,Age >5 and Age <5, below is the complete script
Select a.patientID, a.Location, a.State,(DATEDIFF(YY,b.Age,GetDate())>=5) as [Age >5],(DATEDIFF(YY,b.Age,GetDate()) <5)as [Age < 5] from patient a INNER JOIN PatientDOB b ON a.patientID = b.patientID
thank you
Esakki Muthu10y ago
Hi Abraham,
try this,
Create table Patients(Id Int,Name varchar(50),DOB Datetime)
insert into Patients values(1,'Arnold','2013-1-23')
insert into Patients values(2,'Bossman','2013-6-23')
insert into Patients values(3,'CityBoy','2014-5-23')
insert into Patients values(4,'Drekel','2014-8-23')
insert into Patients values(5,'Emrokin','2015-3-23')
insert into Patients values(6,'Frasher','2016-2-23')
insert into Patients values(7,'Golavendor','2016-7-28')
SELECT * from Patients where DATEDIFF(Month, DOB, GetDate()) <= 12
Manas Mohapatra10y ago
Try like this:
SELECT * FROM tblTemp
WHERE DATEDIFF(month, DOB, GETDATE()) <= 12;
Note: tblTemp is the table name.