How to get an age from two dates in SQL

For a project that I’ve been working on recently I was tasked with finding the Age of a product at a certain point in time in year and months. What should have been a relatively simple task proved to be a bit more difficult as the requirement was that the age be returned in two separate columns: Years and Months.

I approached it by using DATEDIFF as a starting point as that’s what I would want to emulate in order to produce my age.

DATEDIFF(DAY,@StartDate,@EndDate)

I used DAY as the interval as I thought it would be easier to calculate the age in a more granular format, if required.

I then started breaking down the output as working in number of days is a bit unmanageable. I broke the average number of days into a month (30.42) and the number of months in a year (12)

DATEDIFF(DAY,@StartDate,@EndDate)/30.42/12

Then I hit a stumbling block. How was I to split out the month from the year and the year from the month and have them displayed as whole numbers?

To get the year I used the following query:

ROUND((DATEDIFF(DAY,@StartDate,@EndDate)/30.42/12),1)-ROUND((DATEDIFF(DAY,@StartDate,@EndDate)/30.42/12),1)%1

To get the month I used the following query, not as long as the query to produce the year but still similar:

ROUND((DATEDIFF(DAY,@StartDate,@EndDate)/30.42/12),1)%1 * 12

The multiplication of the month by 12 is crucial, as it’s the only way to turn a fraction of a year into a number of months.

I have then wrapped the overall query up within a CTE producing the following query script:

DECLARE @StartDate AS DATE
DECLARE @EndDate AS DATE

SET @StartDate = ‘1969-07-16’
SET @EndDate = GETDATE()
;

WITH Age AS(
SELECT
CONVERT(INT,(ROUND(((DATEDIFF(DAY,@StartDate,@EndDate)/30.42)/12),1))%1 *12 )
AS AgeMonth,
CONVERT(INT,(ROUND(((DATEDIFF(DAY,@StartDate,@EndDate)/30.42)/12),1))-(ROUND(((DATEDIFF(DAY,@StartDate,@EndDate)/30.42)/12),1))%1)
AS AgeYear
)
SELECT AgeYear, AgeMonth
FROM Age