Years months and days since a given data in T-SQL

I had a request for an HR related report that shows years, months and days since the employee started in this format:

yy.mm.dd

e.g. from 29/02/2004 to 12/11/2012 the result should be  8.8.14 (8 Years 8 Months 14 Days)

 

This item on Stack overflow is a pretty good implementation of this, but you can’t always use variables in the code, or use intermediary steps.

Combining that solution into a single query gives this. To implement get rid of the declaration and the initial Select, and replace @date with the date field you are querying:

DECLARE

@date datetime, @years int, @months int, @days int

SELECT @date =‘2/29/04’

SELECT

Convert(varchar,DATEDIFF(yy, @date,GETDATE())CASEWHEN (MONTH(@date)>MONTH(GETDATE()))OR(MONTH(@date)=MONTH(GETDATE())ANDDAY(@date)>DAY(GETDATE()))THEN 1 ELSE 0 END)

+

‘ Years ‘

+

Convert(varchar,DATEDIFF(m,DATEADD(yy,DATEDIFF(yy, @date,GETDATE())CASEWHEN (MONTH(@date)>MONTH(GETDATE()))OR(MONTH(@date)=MONTH(GETDATE())ANDDAY(@date)>DAY(GETDATE()))THEN 1 ELSE 0 END, @date),GETDATE())CASEWHENDAY(@date)>DAY(GETDATE())THEN 1 ELSE 0 END)

+

‘ Months ‘

+

Convert(varchar,DATEDIFF(d,DATEADD(m,DATEDIFF(m,DATEADD(yy,DATEDIFF(yy, @date,GETDATE())CASEWHEN (MONTH(@date)>MONTH(GETDATE()))OR(MONTH(@date)=MONTH(GETDATE())ANDDAY(@date)>DAY(GETDATE()))THEN 1 ELSE 0 END, @date),GETDATE())CASEWHENDAY(@date)>DAY(GETDATE())THEN 1 ELSE 0 END,DATEADD(yy,DATEDIFF(yy, @date,GETDATE())CASEWHEN (MONTH(@date)>MONTH(GETDATE()))OR(MONTH(@date)=MONTH(GETDATE())ANDDAY(@date)>DAY(GETDATE()))THEN 1 ELSE 0 END, @date)),GETDATE()))

+

‘ Days’

 

 

 

5 thoughts on “Years months and days since a given data in T-SQL

    1. The follow up to this is that they changed their minds, realising that a value like 8.8.14 is not particularly useful. They were exporting this data to Excel and trying to do some filtering on that field, and since it doesn’t order alphanumerically and creates too many unique values it is very hard to use for sorting and filtering. I did strongly advise against having it for those reasons, but of course people don’t see the outcomes of these things until they work through the steps and look for themselves. That’s the nature of IT right? You can’t just say no to people because then they think you are an obstacle, but you try to advise people on what it is they really want at the same time.

      The datediff(dateadd()) pattern is pretty useful and fairly well established from a lot of blogs I’ve read, but you need to keep in mind that dateadd only counts boundaries, so for example if you were measuring days from 11:59pm until 12:01am on the very next day, it would return 1, even though this is only 2 minutes later.

      The query looks ugly because it has to compensate for that boundary counting, and also do the months & days separately & also return as a string. So if you’d started in December and were looking at this report in the next month January, the datediff would count that as a 1 year boundary but for the HR team that didn’t make sense because it was really 0 years and 1 month.

Leave a Reply

Your email address will not be published. Required fields are marked *