How to get (format) a Firebird v3 DATEDIFF() to the number of years, months and days all together

firebird

In Firebird 3 I can use DATEDIFF() with either YEAR, MONTH or DAY to get the number of years, months or days respectively.

I need a way to get all-in-one years, months and days.

For example: 412 days should be represented as 1 year, 1 month and 16 days.

Best Answer

are you looking something like this:

declare @d1 datetime, @d2 datetime, @y int, @m int, @d int
set @d1 = '2018-10-26'
set @d2 = '2019-12-12'

set @y = datediff(year, @d1, @d2)
set @m = datediff(month, dateadd(year, @y, @d1), @d2) 
if dateadd(month, @m, dateadd(year, @y, @d1)) > @d2 set @m = @m - 1

set @d = datediff(day, dateadd(month, @m, dateadd(year, @y, @d1)), @d2)
print cast(@y as nvarchar) + ' year(s) ' + cast(@m as nvarchar) + ' month(s) and ' + cast(@d as nvarchar) + ' day(s)'

Results:

1 year(s) 1 month(s) and 16 day(s)

Related Question