Sql server query to get exact DOB in Years, months and days

In this article I am going to explain how to get date of birth (DOB) in years, months and days format in Sql server.

In the previous article I have explained Show and hide div on one button Clickusing jquery, Remove special characters (-, _,) from string in SQL SERVER, Howto create tags using Jquery in asp.net and how to set color for ODD and EVENrows in HTML Table using CSS.

Implementation:

I have got a requirement to calculate the exact date of birth of user in years, months and days format. Here in this article I will share the query that I use to complete this requirement.

SET DATEFORMAT dmy;

declare @DOB date,@currentdate date, @year int,@month int,@day int,@temporaydate date

set @DOB = '30/09/2010'

set @currentdate = getdate()

set @temporaydate = @dob

SELECT @year = DATEDIFF(yy, @temporaydate, GETDATE()) - CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END

SELECT @temporaydate = DATEADD(yy, @year, @temporaydate)

SELECT @month = DATEDIFF(m, @temporaydate, GETDATE()) - CASE WHEN DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END

SELECT @temporaydate = DATEADD(m, @month, @temporaydate)

SELECT @day = DATEDIFF(d, @temporaydate, GETDATE())

SELECT [Years] = @year,[Months]= @month,[days]= @day