Age in year, month and date format in SQL Server

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 = '01/24/1977'

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.