Tuesday 15 October 2013

How to Calculate Age from Date Of Birth using SQL Server

Hi friends, in this article I will explain about How to Calculate Age from Date Of Birth using SQL Server
I already explained in the previous articles about How to get second-highest salary employees in a table,Find 2nd highest salary in SQL server, SQL Query for second maximum salary from employee tableHow to encrypt/decrypt string in sql server and How to delete a null record in SQL Server or in SQL server how to delete rows where multiple columns have either null or Query to delete null or empty values from data table in SQL Server

DB Script:
DECLARE @birth_date DATETIME
SET @birth_date ='1986-09-14 12:34:22'
SELECT Years ,Months,
 CASE WHEN day(@birth_date)>day(getdate())
 THEN day(getdate())+
 datediff(day,@birth_date,dateadd(month,datediff(month,0,@birth_date)+1,0))-1
 ELSE day(getdate())-day(@birth_date)
 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_date),getdate())+
 CASE
 WHEN day(getdate())>=day(@birth_date)
 THEN 0
 ELSE -1
 END AS Months,
 datediff(MINUTE ,convert(varchar(8),@birth_date,108),
 convert(varchar(8),getdate(),108)) AS Minutes
FROM(SELECT
 datediff(year,@birth_date,getdate()) +
 CASE
 WHEN month(getdate())>=month(@birth_date)
 THEN 0
 ELSE -1
 END AS Years
) AS completed
) AS completed

Run the above Query the output will be like as shown in the below figure.

No comments:

Post a Comment

© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.