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.



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


0 Comments