Number of months between two dates

[scoobyroo]

I need to calculate the number of full months between two dates. For example, 11/1/07 and 2/29/08. I need the number of full months between these two dates to show as 4. Using the standard datediff formula only shows 3 months.
I also tried the following formula that I found online:

Datediff(mm, 0, Dateadd(dd, Datediff(dd, 0, @EndDate)-Datediff(dd, 0, @BeginDate), 0))

This works most of the time, but when I try to get the difference between 11/1/07 and 6/30/08, it returns 7 instead of 8.

What am I missing?

 

[gmmastros]

DateDiff returns the number of 'transitions' encountered.

Ex. Feb 29, 2008 -> Mar 1, 2008.

This is one day, but datediff returns 1 month.

[tt][blue]Select DateDiff(month, '20080229', '20080301')[/blue][/tt]


But, when you do Mar 1, 2008 -> Mar 31, 2008, you will get 0 months.

[tt][blue]Select DateDiff(month, '20080301', '20080331')[/blue][/tt]

The problem you are experiencing is caused by your definition for your 'end date'. I recommend you add 1 to your end date for comparison purposes.

Declare @Start DateTime
Declare @End DateTime

Set @Start = '11/1/07'
Set @End = '2/29/08'

Select DateDiff(Month, @Start, @End [!]+ 1[/!])

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

George, I looked at this and thought the answer would be add one but I did not now the reason. I also thought about subtracting one which would have been wrong if done at the same time as the add one. I hope scoobyroo likes your answer.
djj

 

[scoobyroo]

Thanks for the reply. I thought about adding one, but wasn't sure that was the best solution. This is the formula I will use.

 

[SQLSister]

Technically the number of full months is 3 inyour first example becasue you haven't fished out that last month. If you are using a begin and end date for a report, why not use the first of the month instead of the last day of the month?

"NOTHING is more important in a database than integrity." ESquared