Get Age in Year, Month and Day Format in SQL Server

Updated on August 30, 2015     Kisan Patel

This tutorial will show you how to get Age in year, month and date format in SQL Server?

To get age in Year, month and date, we need to manipulate the given date with DATEDIFF and other helping functions.

	DECLARE @givenDate datetime
	DECLARE @tempDate datetime
	DECLARE @years int, @months int, @days int
	SELECT @givenDate = '07/23/1989'
	SELECT @tempDate = @givenDate
	-- get year
	SELECT @years = DATEDIFF(yy, @tempDate, GETDATE()) -
	CASE
	WHEN
	(MONTH(@givenDate) > MONTH(GETDATE()))
	OR
	(MONTH(@givenDate) = MONTH(GETDATE())
	AND
	DAY(@givenDate) > DAY(GETDATE()))
	THEN 1
	ELSE 0
	END
	SELECT @tempDate = DATEADD(yy, @years, @tempDate)
	-- get months
	SELECT @months = DATEDIFF(m, @tempDate, GETDATE()) -
	CASE
	WHEN
	DAY(@givenDate) > DAY(GETDATE())
	THEN 1
	ELSE 0
	END
	SELECT @tempDate = DATEADD(m, @months, @tempDate)
	-- get days
	SELECT @days = DATEDIFF(d, @tempDate, GETDATE())
	-- output the result
	SELECT CAST(@years as varchar(5)) + ' years ' +
	CAST(@months as varchar(3)) + ' months ' +
	CAST(@days as varchar(3)) + ' days'

In the above code snippet, we are declaring few variables to hold given date, temporary date to calculate on and calculated date, months and year.

First, we are calculating the year portion of the age by subtracting the year from 1 or 0 based on whether the current month is greater than given month or not.

Next, we are calculating months and the days and at last we are casting years, months and days values into varchar and giving as output.