Difference between dates in months with decimal return

2

Good morning!

I am using the command below and the result returned is 6. In fact, observing the dates well, it has not closed exactly the 6 months, I think that through this function days are not being considered, and this is causing me a problem. Would it have any way of returning in this case only the integer 5 or with the same decimal, 5,8 for example?

SELECT CONVERT(VARCHAR(10), GETDATE(), 112), '20180222', DATEDIFF(MM, '20180222', CONVERT(VARCHAR(10), GETDATE(), 112))
20180817  20180222    6
    
asked by anonymous 17.08.2018 / 16:03

3 answers

2

I started searching but I did not find a way to improve the accuracy of datediff via parameter, so I decided to go to 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 (for example, day 17), it is subtracted 1 from the total months; being day 22, the total calculated by the function is returned.

    
17.08.2018 / 16:26
2

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 numeric cast (14,2), that is, 14 long before the comma and 2 decimal places, and splitting the days by 30 (one month), thus resulting in 5.86666

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

    
17.08.2018 / 16:24
0

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

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

Here's the fiddle running: SQL Fiddle

    
17.08.2018 / 16:56