CODEWITHSUNDEEP
sqlsql-servert-sqldate
J. Swapp
J. Swapp

Reputation: 1

Calculate the years and months it has been from a prescribed date from a table column?

I need to take the purchasedate from a table and calculate the total years plus the decimal equivalent of months left over from today's date and be able to present this calculated data in yyy.mm.

i.e 4.5 would be deciphered as 4 years and 6 months from purchasedate.

Upvotes: 0

Views: 61

Answers (2)

Dale K
Dale K

Reputation: 27201

If you work out the difference in months, then divide by 12 to get years, then use the remainder to get months which you can convert into a fraction e.g.

declare @PurchaseDate date = '01 Jan 2015', @Now date = current_timestamp

select convert(varchar(3),datediff(month,@PurchaseDate,@Now)/12) + '.' + substring(convert(varchar(5),convert(decimal(9,2),(datediff(month,@PurchaseDate,@Now)%12)/12.0)),3,2)

Gives

5.33

It might need some tweaking for edge cases.

Note the divide by 12.0 to force a fractional result - otherwise you just get an integer.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Just multiply by 12 and add months:

select dateadd(month, floor(@years * 12), purchasedate)

Upvotes: 0

Related Questions