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.


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)


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:


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:


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

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

One thought on “How to get an age from two dates in SQL

  1. Marylin says:

    Good post. I learn something new and challenging
    on websites I stumbleupon on a daily basis. It’s always interesting to read through content from other authors and
    practice something from other websites.

Comments are closed.