How to use datediff to get month and year in decimal form?

Question

question

dbwill54 asked Aug 3, '20

How to use datediff to get month and year in decimal form?

Here is what i have:

,CAST(DATEDIFF(month, a.LstPaymentDte, a.createdte)/12 as DECIMAL(10, 1)) AS diff_in_month

for example last pay date = 6/14/2019 and createte = today.

I want to see the value return as 1.1 for 1 year and 1 month ago.

datediff

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-08-03T12:10:22Z

Dnirmania answered Aug 3, '20

You can use below query. for your desired results.

SELECT cast(DATEDIFF(MONTH,a.LstPaymentDte,a.createdte)/12 AS varchar) + 
' Year(s) '+ cast(DATEDIFF(MONTH,a.LstPaymentDte,a.createdte)%12 AS varchar) + ' month(s)'

or for 1.1 format 
SELECT cast(DATEDIFF(MONTH,a.LstPaymentDte,a.createdte)/12 AS varchar) + 
'.'+ cast(DATEDIFF(MONTH,a.LstPaymentDte,a.createdte)%12 AS varchar)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question