Calculate Age from Date Of Birth using SQL Server  

Posted by ReelTym


declare @birth datetime, @now datetime
select @birth ='19660527 12:34:22', @now = getdate()
select
years,
months,
days =
case when day( @birth ) > day( @now )
then
day( @now )
+ datediff(
day,
@birth,
dateadd(
mm,
datediff( mm, 0, @birth ) + 1,
0
)
) - 1
else day( @now ) - day( @birth )
end,
hours =
datepart(
hh,
convert( varchar(10), dateadd( mi, minutes, 0 ), 108 )
),
minutes =
datepart(
mi,
convert( varchar(10), dateadd( mi, minutes, 0 ), 108 )
)
from (
select
years,
months =
datediff(
mm,
dateadd( yy, years, @birth ),
@now
) +
case
when day( @now ) >= day( @birth )
then 0
else -1
end,
minutes =
datediff(
mi,
convert( varchar(8), @birth, 108 ),
convert( varchar(8), @now, 108 )
)
from (
select
years =
datediff( yy, @birth, @now )
+ case
when month(@now)>=month(@birth) then 0
else -1
end
) t
) as t

This entry was posted on Thursday, September 16, 2010 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments