I am writing a little query in SQL and am butting heads with an issue that it seems like someone must have run into before. I am trying to find the number of months between two dates. I am using an expression like ...

DATEDIFF(m,{firstdate},{seconddate})

However I notice that this function is tallying the times the date crosses the monthly threshold. In example...

Interesting, I never noticed it did that.
–
user937146Sep 13 '11 at 22:35

1

How many days are in a month? What answer do you want given the date pairs (2011-03-15, 2011-04-14), (2011-03-15, 2011-04-15), (2011-03-15, 2011-04-16), (2011-01-28, 2011-02-28), (2011-01-28, 2011-03-01), (2012-01-29, 2012-02-28), (2012-01-29, 2012-02-29), (2012-01-29, 2012-03-01), (2012-01-30, 2012-02-29), (2012-01-30, 2012-03-01), and so on. There tends to be a concentration on dates around the end of February in leap years and non-leap years, but similar questions arise with other end-of-month dates such as (2011-05-31, 2011-06-30).
–
Jonathan LefflerSep 14 '11 at 0:11

4 Answers
4

If you want to find some notional number of months, why not find the difference in days, then divide by 30 (cast to FLOAT as required). Or 30.5-ish perhaps - depends on how you want to handle the variable month length throughout the year. But perhaps that's not a factor in your particular case.

The following statements have the same startdate and the same endate. Those dates are adjacent and differ in time by .0000001 second. The difference between the startdate and endate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1. ...
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000'); ....

(from DATEDIFF, section datepart Boundaries ). If you are not satisfied by it, you probably need to use days as unit as proposed by martin clayton

The original has got > where you've got >=, which means if day parts are equal, the result of DATEDIFF will still be decreased by 1. Another thing is, calculating the difference of months between dates like 2011-03-31 and 2011-06-30. This method will return 2, because 31 is indeed greater than 30. However June cannot have more than 30 days. So is 2 months in this case all right?
–
Andriy MSep 13 '11 at 23:37

1

Good points. In this case it is not overly critial however in certain situations it may be
–
JBoneSep 14 '11 at 12:02

DATEDIFF is like this by design. When evaluating a particular time measurement (like months, or days, etc.), it considers only that measurement and higher values -- ignoring smaller ones. You'll run into this behavior with any time measurement. For example, if you used DATEDIFF to calculate days, and had one date a few seconds before midnight, and another date a few seconds after midnight, you'd get a "1" day difference, even though the two dates were only a few seconds apart.

DATEDIFF is meant to give a rough answer to questions, like this:

Question: how many years old are you?
Answer: some integer. You don't say "I'm 59 years, 4 months, 17 days, 5 hours, 35 minutes and 27 seconds old". You just say "I'm 59 years old". That's DATEDIFF's approach too.

If you want an answer that's tailored to some contextual meaning (like your son who says "I'm not 8! I'm 8 and 3-quarters!, or I'm almost 9!), then you should look at the next-smallest measurement and approximate with it. So if it's months you're after, then do a DATEDIFF on days or hours instead, and try to approximate months however it seems most relevant to your situation (maybe you want answers like 1-1/2 months, or 1.2 months, etc.) using CASE / IF-THEN kinds of logic.

If you turned 32 in December and today's January, you don't say that you are 33. And yet DATEDIFF(YEAR) would say that you are.
–
Andriy MSep 13 '11 at 23:45

@Andriy -- it was just a gross example. The point was, it's not broken -- it was designed to give only rough answers. It has a plus-or-minus (error bounds) of 1 (of whatever measurement you're using).
–
VanHalenSep 13 '11 at 23:51

Yes, I agree, it is by design that it works the way it does. And I agree that there's something common in how both DATEDIFF and people tend to answer roughly, with an approximate integer. It's just that DATEDIFF always uses the same rules, while people don't. Sometimes we round, like in twenty minutes past five, when it's actually only eighteen, and at other times we tend to report a whole number of complete units, without any rounding, like with age in years. And I think it's the latter that the OP wants, the way of obtaining a result (an integer too) without rounding.
–
Andriy MSep 14 '11 at 0:06