DateDiff Function February question

I'm executing the following datediff function and getting
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
Vic
DATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic
|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.

>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.[vbcol=seagreen]
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
getting
>
>.
>
|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...[vbcol=seagreen]
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
> regardless of when in
> day of the month.
> should tweak it to get
> instead of
> change based on
> format instead.
> getting
|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
[vbcol=seagreen]
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>regardless of when in
>
>day of the month.
>
>should tweak it to get
>
>instead of
>
>change based on
>
>format instead.
>
>getting
>