TSQL format the date difference to be "YY years MM months and DD days"

Question

Ok this is a little bit of a strange request. We are trying to get a formatted "age" statement to come out in a report in PeopleSoft, it provides a "TSQL" based builder which is fairly unhelpful. We cannot use stored functions and we cannot edit the entire SQL statement as one thing. All we can do is say field by field what the formula is, then the tool will join it all the elements together to produce the query.

So, given that restriction how can we get the difference between two dates to be formatted as a nicely human readable sentence eg. "14 years, 3 months and 10 days"

Any ideas would be greatly appreciated.

Answer 1

If you can use string concatenation, this will give you the number of years:

DATEDIFF(yy, t.startdate, t.enddate) 

This will give you the months:

DATEDIFF(mm, 
         DATEADD(yy, 
                 DATEDIFF(yy,  
                          t.startdate, 
                          t.enddate), 
                 t.startdate), 
         t.enddate)

And this will give you the days value:

DATEDIFF(dd, 
         DATEADD(mm, 
                 DATEDIFF(mm, 
                          DATEADD(yy, 
                                  DATEDIFF(yy, 
                                           t.startdate, 
                                           t.enddate), 
                                  t.startdate), 
                          t.enddate), 
                          DATEADD(yy, 
                                  DATEDIFF(yy, 
                                           t.startdate, 
                                           t.enddate), 
                                  t.startdate)), 
         t.enddate)

You can see & test using this saved query I posted on the StackExchange Data Explorer. It uses Azure, with uses TSQL...

Answer 2

You can use whatever formula you like. You simply put the return type, and the SQL function in the expression tab, then use the expression as a field.