Sunday, July 25, 2010

Calculate Age from Date Of Birth using SQL Server

Here’s a query to calculate the Age in Years, Months, Days, Hours and Minutes

declare @birth_day datetime
set @birth_day ='19660527 12:34:22'
select

years,
months,
case
when day(@birth_day)>day(getdate())
then day(getdate()) + datediff(day,@birth_day,dateadd(month,datediff(month,0,@birth_day)+1,0))-1
else day(getdate())-day(@birth_day)
end as days,
datepart(hour,convert(varchar(10),dateadd(minute,minutes,0),108)) as hours,
datepart(minute,convert(varchar(10),dateadd(minute,minutes,0),108)) as minutes
from
(
select
years,
datediff(month,dateadd(year,years,@birth_day),getdate())+
case
when day(getdate())>=day(@birth_day)
then 0
else -1
end as months,
datediff(minute,convert(varchar(8),@birth_day,108),
convert(varchar(8),getdate(),108)) as minutes
from
(
select
datediff(year,@birth_day,getdate()) +

Read more: SQL Server curry

Posted via email from .NET Info