someone people tell me why I am not right...
SELECT DATEDIFF(MONTH,'2015-04-01','2015-08-01')
/*
Returns: 4
BUT it should be months not 8-4, so 5
*/
DECLARE @MNTHS table(months date)insert into @mnths values('2015-04-01'),
('2015-05-01'),('2015-06-01'),('2015-07-01'),('2015-08-01')
select count(*) from @mnths as [the real count]
what?
and btw, great looking new site!!!!
8 minus 4 = 4 ... no?
DATEDIFF is really telling you the number of boundary crossings. April 1 to August 1 crosses four month boundaries. So does April 1 to Aug 31,, or April 30 to Aug 1 and so on. All of the following will return the same result
SELECT DATEDIFF(MONTH, '2015-04-01', '2015-08-01') ,
DATEDIFF(MONTH, '2015-04-01', '2015-08-31') ,
DATEDIFF(MONTH, '2015-04-30', '2015-08-01')
I hear what you are saying, but I don't like the idea the starting month isn't counted because by your account it is never crossed.... that is backwards.... MONTH should be the full range between, but meh... +1 to fix the bug, no biggie
yes.....
april + may + june + july + august = 5, no 
By that logic, April 30th through August 1st should be 3 months (and a tiny bit left over if you want to be that a picky), shouldn't it? 
Possibly, but I think our disconnect could come from you evaluation of the full date and not the datepart of month, in it's simplest form it should be able to take what month I start with and count every one until it gets to the end date.... MONTH...
What should be the result of DATEDIFF(month, '20150401', '20150401')? By the OP's logic, that would be 1.
I'd say in this scenario 0 is correct since there is no MONTH difference
I'm not sure my logic is being capture as I'm meaning to display it... I feel the MONTH parameter indicates the part of the date to be evaluated. That's why we have d y m.
If DATEDIFF(month, '20150401', '20150401') = 0 then does DATEDIFF(month, '20150401', '20150501') = 1 or 2?
it equals one, i'm not crazy man... but i'm saying it should equal two, april + may... MONTH part should not be calculated by 5-4
okay maybe this might help explain my logic... I stand on-top of my soap-box on 4-01-2015, and again on 5-01-2015, how many times did I stand on my soap-box for the aggregated months...... if you don't say two, i need to start asking for some serious code reviews.
so for you math peeps, lets say I'm calculating the total payment of a loan using datediff, i enter the start date of the loan, and the end date, with the current logic the debtor get a free month... I know logic would need to be introduced to account for day, but just at its simplest form......datediff(m,start,end)*min_payment = total
anyways......
----------------------------------------------------------------
----------------------------------------------------------------
DECLARE @INITIAL_NEXT_DUE DATE = '2015-07-06',
@TOTAL_PAY_PLUS_DIF MONEY = 324.25,
@D DATE = CAST(GETDATE() AS DATE),
@MIN_DUE MONEY = 323.23,@UNO INT = 1,@ERO INT = 0;
----------------------------------------------------------------
SELECT
--
--THIS PART CHECKS THE DAY TO SEE IF A PAYMENT SHOULD APPLY,
--A 1 WILL THEN BE MULTIPLIED BY THE MIN_DUE
--
CASE WHEN
(DATEPART(MONTH,GETDATE())-DATEPART(MONTH,@INITIAL_NEXT_DUE))
+(DATEPART(DAY,GETDATE())-DATEPART(DAY,@INITIAL_NEXT_DUE))
+(DATEPART(YEAR,GETDATE())-DATEPART(YEAR,@INITIAL_NEXT_DUE))
>DATEPART(DAY,@INITIAL_NEXT_DUE) THEN @UNO ELSE @ERO
END*@MIN_DUE
--
--THIS PART TAKES THE DIFFERENCE BETWEEN NOW AND THE SEED DATE
--TIMES THE MIN_DUE TO ADD BY THE ABOVE TO GET THE DUE AMOUNT
--
+(DATEDIFF(MONTH,@INITIAL_NEXT_DUE,@D)*@MIN_DUE)
--
--NOW SUBTRACT THE TOTAL PAID TO GET THE CURRENT DUE
--
-@TOTAL_PAY_PLUS_DIF AS [Billing.Next_Due_Amt]That's an "inclusive" count ... if you want that answer then just add one to DATEDIFF 
Or perhaps? add one to DATEDIFF if the DAY of the later date is larger than the Day of the earlier one ... e.g.
05-Jan-2015 to 04-Feb-2015 - DATEDIFF = 1
05-Jan-2015 to 06-Feb-2015 - DATEDIFF = 1 but DAY is later so add an extra 1 ??
Dunno about 05-Jan-2015 to 05-Feb-2015 - you'll have to decide what your Business Rule is for that case
+1 to fix the bug, no biggie
and
(DATEPART(MONTH,GETDATE())-DATEPART(MONTH,@INITIAL_NEXT_DUE))
+(DATEPART(DAY,GETDATE())-DATEPART(DAY,@INITIAL_NEXT_DUE))
+(DATEPART(YEAR,GETDATE())-DATEPART(YEAR,@INITIAL_NEXT_DUE))
>DATEPART(DAY,@INITIAL_NEXT_DUE) THEN @UNO ELSE @ERO
END*@MIN_DUE
Math people..........I just never seem to connect with math people...... well anyway
But what MONTH is addressing is how many complete months; i.e. a whole egg has a shell and is counted as one egg, but would you call a bit of the shell? One egg?
I appreciate everyones time, here is my take away.... it is factored like an array, STARTING with 0.... but I still don't like it....I was interpreting the datediff function to return the difference between the input parameter.... and for me.. that means 5 unless viewed as an array.
thanks everyone
Why not just add +1 to your formula? That would solve your issues and not require every implementation of SQL to be rewritten. The documentation states that DATEDIFF measures the number of boundaries crossed for that date part, which is what you want: DATEDIFF for month of 4/1/2015 and 4/20/2015 is 0, not 1.
If you want a decimal, why not do a datediff with days and then divide by 30.42?
I think you need to get over it
DATEDIFF operates the way it is documented, but you seem to think it is a bug because that isn;t how you want it to work. What you want is different to how it is documented, and something you can easily adjust (i.e. "Add one")
For me "one months difference" would be "Later than this day-number next month" and certainly would not give a count of One Month from 31-Jan to 01-Feb ... but that isn't how DATEDIFF is documented either!!
I'll go there....meh why not....
bound•a•ry (ˈbaʊn də ri, -dri)
n., pl. -ries. 1. something that indicates bounds or limits, as a line. 2. Math. the collection of all points of a given set having the property that every neighborhood of each point contains points in the set and in the complement of the set. [1620–30]
Okay riddle me this.... if I'm standing in Colorado, get a wild hair and head to Utah.... how many state lines did I cross....
did April's boundary not get crossed?
If a boundary represents a enclosure of sorts surround something, like the circles above around the months, or a state's line around the entire state... If I start somewhere I have to cross it's boundary to get out and the new boundary I'm enter into.... the exit shouldn't count twice because it is the same boundary already counted when entering......
AND I stated in my first post +1 to fix the bug which I think is escaping you.... let me rephrase, you can +1 after the datediff function to fix the bug.... and yes the bug 
or it all works if you see it like this, I guess (see below)
As you stated, you are able to get the result that you want by adding one to result of DATEDIFF. So this discussion is just of academic interest at this point.
However, your conjecture/assertion that the documented, observed behavior of DATEDIFF is a bug is not correct. Continuing with your dictionary definition of a line, here is why:
A line has zero width. If you are familiar with open sets and closed sets in math, all time points in April form an open set, and so does all time points in May. The boundary between those two sets is a shared boundary. It is not that there is an ending boundary of April and then there comes the beginning boundary of May. There is no no-man's land in between. Not even of one atto-second.. There is just one boundary. Just as in the case of Colorado and Utah, where there is no no-man's land.
Another way to think about it is that there is no interval of time that is both in April and May, no matter how small you make that interval.
So you cross four boundaries (albeit shared boundaries) to get from April to August. And, you cross a single boundary to get from Colorado to Utah.
Your crossed the shared boundary of April and May.