Adatis BI Blogs

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