Battling Datediff in SQL

Question

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...

DATEDIFF(m,3/31/2011,4/1/2011) will yield 1  
DATEDIFF(m,4/1/2011,4/30/2011) will yield 0 
DATEDIFF(m,3/1/2011,4/30/2011) will yield 1

Does anyone know how to find the months between two dates more-so based upon time passed then times passed the monthly threshold?

Answer 1

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.

Answer 2

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