Difference between dates in months with decimal return

Question

Good morning !

I am using the command below and the result returned is 6. In fact, looking at the dates, I haven’t exactly closed the 6 months, I think that through this function are not being considered the days, and this is causing me a problem. Would it have any way to return in this case only the integer 5 or even with decimal, 5.8 for example ?

SELECT CONVERT(VARCHAR(10), GETDATE(), 112), '20180222', DATEDIFF(MM, '20180222', CONVERT(VARCHAR(10), GETDATE(), 112))

20180817  20180222    6

Answer 1

It’s not exact, but it comes close to what you want:

SELECT CONVERT(VARCHAR(10), GETDATE(), 112), '20180222', CAST(DATEDIFF(DD, '20180222', CONVERT(VARCHAR(10), GETDATE(), 112)) AS numeric(14,2)) / 30

I am giving a cast of Numeric (14,2), ie 14 long before the comma and 2 decimal places, and divine the days at 30 (one month), thus resulting in 5,86666

It is not exact because there are months with 30 and months with 31, and February has 28..

Answer 2

I started researching but couldn’t find a way to improve the accuracy of the Datediff via parameter, so I decided to leave for logic:

declare @diferencaDias int, @meses int
select @diferencaDias = CAST(DATEDIFF(DD, DATEADD(MM, DATEDIFF(MM, '20180222', CONVERT(VARCHAR(10), GETDATE(), 112)), '20180222'), CONVERT(VARCHAR(10), GETDATE(), 112)) AS VARCHAR(2)),
  @meses = DATEDIFF(MM, '20180222', CONVERT(VARCHAR(10), GETDATE(), 112))


select CONVERT(VARCHAR(10), GETDATE(), 112), '20180222', case when @diferencaDias < 0 then @meses - 1 else @meses end

The code checks the difference in days and months between dates. Considering the 6 months of the example, if it has not yet been completed (as for example day 17), it is subtracted 1 of the total of months; being day 22, the total calculated by the function is returned.

Answer 3

To have the difference in decimals, you need to calculate in hours, and convert later: multiply by 24 to know in days and by 30 later to know in months:

 select cast(datediff(hour,convert(datetime,'20180222'),
                      getdate())AS DECIMAL(6,2))/(24*30)

Here the fiddle running: SQL Fiddle