0

I've tried to calculate the number of months using datediff in SQL Server, but when I calculate:

Select DATEDIFF(Month,'1/1/2019','12/31/2019')

It returns 11 so I increment the value by 1 to be 12. But when I calculate:

select DATEDIFF(Month,'11/19/2019','11/18/2020') + 1

it returns 13 and that's wrong.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Qais Almomany
  • 31
  • 6
  • 1
    `DATEDIFF` returns the number of "ticks" between 2 dates. For example `DATEDIFF(DAY,'2020-01-20T23:59:59.9999999','2020-01-21T00:00:00.0000000')` returns `1` even though there is only 0.0000001 seconds between the 2 times. – Thom A Jan 21 '20 at 10:38
  • But result of DATEDIFF(Month,'11/19/2019','11/18/2020') is 12 that's why you get 13 – Kivayan Jan 21 '20 at 10:38
  • 2
    ["This function returns the count (as a signed integer value) of the specified *datepart boundaries* crossed between the specified startdate and enddate."](https://learn.microsoft.com/sql/t-sql/functions/datediff-transact-sql) There are 12 such boundaries between a date in November 2019 and one in November 2020, but only 11 when you stay in the same year. You may be looking for [this](https://stackoverflow.com/q/1106945/4137916). – Jeroen Mostert Jan 21 '20 at 10:39
  • @QaisAlmomany . . . It is not "wrong". It is doing the calculation EXACTLY as it is documented. Your question is unclear, because you don't explain why think it is wrong. Your two examples also use different dates, so it is not surprising that the numbers are different. – Gordon Linoff Jan 21 '20 at 12:00

0 Answers0