1

Is there any way to calculate the number of months between two dates?

I have this 

SELECT DATEDIFF(mm, '2015-11-01', '2015-12-01')

but this brings me 1 and in fact I want it to be 2, I should sum 1 to the result or there is another function specifically for this?

Let me explain what I need, I have months and years, I don't care about the day because is always 01, so each date is represented with yyyy/mm/01, in my case I identify a value and this values appears on '2015-11-01' and '2015-12-01', this means I have it in two months, now I need to calculate the number of months, so with datediff returns 1 which is not right in my case.

carlosm
  • 687
  • 2
  • 14
  • 29
  • 1
    http://stackoverflow.com/questions/1106945/calculating-number-of-full-months-between-two-dates-in-sql – mohan111 Feb 24 '16 at 13:53
  • 2
    What is the logic behind you wanting it to be 2? If they were in the same month, would you want the "difference" to be 1? If they were on the same day? If so, then yes, always add 1 to the DATEDIFF, and you will get the result you want. – Tab Alleman Feb 24 '16 at 13:54
  • IF your dates alwyas start from the first day just add a +1 to the result or CEIL the number of days over 31 for a quick hack – Mihai Feb 24 '16 at 13:54
  • 1
    @TabAlleman I *think* it's that there are 2 different months in that date range. So, yes, it should be `DATEDIFF + 1` – Lamak Feb 24 '16 at 13:54
  • Whey do you expect this to be 2? You might need to define a bit more exactly what you consider a month to be. – Paddy Feb 24 '16 at 13:55

2 Answers2

7

If I understood your question correctly, This will be my suggestion:

Just add 1 to your DATEDIFF output.

SELECT DATEDIFF(mm, '2015-11-01', '2015-12-01') +1
Sankar
  • 6,908
  • 2
  • 30
  • 53
2

You can do this as below

SELECT 12 * (YEAR(Date1) 
              - YEAR(Date2)) 
       + (MONTH(Date1) 
           - MONTH(Date2)) AS months 
FROM table

OR

SELECT TIMESTAMPDIFF(MONTH, '2015-11-01', '2015-12-01')
Dipesh Parmar
  • 27,090
  • 8
  • 61
  • 90