Calculate Year, Month and Day between Current and specified Date in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

hello, 

i have datetime field in sql, i want to fetch age calculation in sql query like 

dob: 01/01/1983

sql query to return

Years- Months- Days  (36 Year, 2 Months, 5 days) 

please advice

Posted one year ago

 

select SName,
FName,
DOB,
DATEDIFF(YY,DOB,GETDATE()) AS AGE,
CAST(DATEDIFF(yy, DOB,getdate()) AS varchar(4)) +' Years, '+ 
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, DOB, getdate()), DOB), getdate()) AS varchar(2)) +' Month,and '+ 
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, DOB, getdate()), DOB), getdate()), DATEADD(yy, DATEDIFF(yy, DOB, getdate()), DOB)), getdate()) AS varchar(2)) +'Days' AS Ages 
FROM tblData

 


Posted one year ago

thank you but it looks very long query, is there any short version of it


Posted one year ago

Hinauna,

There is no short version of query.

If you want to make it short then Create function and call it where you need.

Refer the below link and modify it.

Display time elapsed few seconds ago, minute ago, hours ago, days ago and year ago in SQL Server