SQL Script to Calculate Age from Date Of Birth

Leave a comment (0) Go to comments

Last week, I got a assignment to calculate age from the date of birth. It was pretty simple, I used the following TSQL script find out the age precisely up to minutes.

 

declare @birth_day datetime

set @birth_day = '19790510 07:10:00' --- Date of birth for which, you wanted to calculate age in years, months, days, hours and minutes


select

 years,

 months,

 abs(datediff(day,getdate(),dateadd(year,years,

  dateadd(month,months,@birth_day)))) 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,

 abs(datediff(day,getdate(),dateadd(year,years,@birth_day))) as days,

 datediff(minute,convert(varchar(8),@birth_day,108),

   convert(varchar(8),getdate(),108)) as minutes

from

(

select

 datediff(year,@birth_day,getdate()) +

 case

  when month(getdate())>=month(@birth_day) then 0

  else -1

 end as years

) as t

) as t

 

EOF - SQL Script to Calculate Age from Date Of Birth, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.