2

I have a problem in counting month's difference from 2 date with sql server query

i tried using DATEDIFF(MONTH, SD, ED)

this for example

SD = '2013-12-10 00:00:00.000'
ED = '2014-12-09 00:00:00.000'
SELECT DATEDIFF(MONTH, SD, ED)

--result : 12 //this correct result


SD = '2013-12-10 00:00:00.000'
ED = '2014-12-10 00:00:00.000'
SELECT DATEDIFF(MONTH, SD, ED)

--result : 12 //this incorrect result, the result that i want is 13


SD = '2013-12-10 00:00:00.000'
ED = '2014-12-15 00:00:00.000'
SELECT DATEDIFF(MONTH, SD, ED)

--result : 12 //this incorrect result, the result that i want is 13

SD = '2013-12-01 00:00:00.000'
ED = '2014-11-30 00:00:00.000'
SELECT DATEDIFF(MONTH, SD, ED)

--result : 11//this incorrect result, the result that i want is 12

is there any solution to get correct result of month difference of 2 date?? can someone help me?

Thanks

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Hansen
  • 650
  • 1
  • 11
  • 32
  • 1
    Define your vision of the "*correct result*" for any two given dates. – PM 77-1 Dec 24 '14 at 04:21
  • Datediff looks like the wrong solution, although if you add a month to ED and then subtract a day (or possibly the other way round) it might work. You might get better answers if you describe what you want to count. the examples are confusing and not precise enough to give an exact answer. – Jasen Dec 24 '14 at 04:37

2 Answers2

5

DATEDIFF only compares the months regardless of the dates within. As you're wanting to include part-months, try comparing the dates as well, and adding CASE WHEN DATEPART(day,@d2) >= DATEPART(day,@d1) THEN 1 ELSE 0 END

So to use the notation from your question:

SELECT DATEDIFF(MONTH, SD, ED) +
    CASE WHEN DATEPART(DAY, ED) >= DATEPART(DAY, SD) THEN 1 ELSE 0 END
Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • The same thing I specified in my edit but he gave some other example and said its giving Incorrect result – Rajesh Dec 24 '14 at 06:23
2

DATEDIFF() works differently than many people expect. From the official documentation:

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

DATEDIFF(MONTH, @startdate, @enddate) is equivalent to 

(YEAR(@enddate) - YEAR(@startdate)) * 12 + (MONTH(@enddate) - MONTH(@startdate)).

So for 2013-12-01 and 2014-11-30, its (2014-2013) * 12 + (11 - 12)

which is indeed 11. For '2013-12-31' and '2014-11-01' it is also 11. Again, it's done this way because it's the only culture-neutral and fully consistent way to do it.

You will need to define what "correct" is in terms of your application and then write your query as such. If you need to use the logic repeatedly, a user-defined function is recommended.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • @RobFarley No, it's stupid. As in lacking any intelligence for understanding what the user wants, and in my experience what a user *actually* wants is *never* what `DATEDIFF()` actually does, yet it's the only default function that does this operation. Thus: *It's always non-intuitive*. It works so for good reason (a function that does what all users want would be too complex), but that makes it very limited (or, as I said: stupid). It's not prefabricated structure. It's just a tool you can use to build one yourself. That's actually kind of unusual in SQL. – Bacon Bits Dec 24 '14 at 08:07