I was curious if there is a bug in DATEDIFF when dealing with years. The
months seem to work correctly, but the Years are all over the place.
Here is some examples:
select
DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
select
DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
select
DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
-- --
1 6 Should be 0 and 6
(1 row(s) affected)
-- --
1 15 Should be 0 and 15
(1 row(s) affected)
-- --
2 18 Should be 1 and 18
(1 row(s) affected)
Am I missing something?
Thanks,
Tom(a) never use such ambiguous date formats. I have no idea whether that
first date is November 1 2004, January 11 2004, January 4th 1911, or January
4th 2011. The server you are on is maybe guessing right, but what if you
run this code on a different server with different SET DATEFORMAT or regionl
settings?
(b) DATEDIFF(YEAR, measures the number of years that have passed. So,
DATEDIFF(YEAR, '20051231','20060101') = 1
DATEDIFF(YEAR, '20050101','20061231') = 1
This result is expected, and is not a bug. Basically, you can pretend that
it strips the month/day parts from the date (and time if that is supplied as
well), and just subtracts the integer derived from DATEPART(YEAR,
<dateValue> ).
If you're trying to calculate age based on birthdays, it's a little more
complex than a simple DATEDIFF.
http://www.aspfaq.com/2233
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e5bgY%23UFGHA.2204@.TK2MSFTNGP10.phx.gbl...
> Running on Sql Server 2000.
> I was curious if there is a bug in DATEDIFF when dealing with years. The
> months seem to work correctly, but the Years are all over the place.
> Here is some examples:
> select
> DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
> select
> DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
> select
> DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
> -- --
> 1 6 Should be 0 and 6
> (1 row(s) affected)
>
> -- --
> 1 15 Should be 0 and 15
> (1 row(s) affected)
>
> -- --
> 2 18 Should be 1 and 18
> (1 row(s) affected)
> Am I missing something?
> Thanks,
> Tom
>|||There is no bug, it is working as it was meant to. All it does, I believe,
is take the year into account, does not do anything clever with the
respective position within each year.
"tshad" wrote:
> Running on Sql Server 2000.
> I was curious if there is a bug in DATEDIFF when dealing with years. The
> months seem to work correctly, but the Years are all over the place.
> Here is some examples:
> select
> DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
> select
> DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
> select
> DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
> -- --
> 1 6 Should be 0 and 6
> (1 row(s) affected)
>
> -- --
> 1 15 Should be 0 and 15
> (1 row(s) affected)
>
> -- --
> 2 18 Should be 1 and 18
> (1 row(s) affected)
> Am I missing something?
> Thanks,
> Tom
>
>|||>> Am I missing something?
Yes, you are assuming DATEDIFF function on two dates returns the completed
year, month etc, which is not accurate.
DATEDIFF simply returns the number of date and time boundaries crossed
between two specified dates. And the results you got are consistent.
Anith|||select DATEDIFF(YEAR,'20051231','20060101'),
DATEDIFF(YEAR,'20050101','20051231')
Returns 1 and 0 even though in days, the first one is 1 day and the second
364 days.
DateDiff with years returns in reality the number of times January 1 is
"crossed".
This is what BOL has to say:
"Returns the number of date and time boundaries crossed between two
specified dates."
This is true for Datediff with years, months, w
s, days..."tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e5bgY%23UFGHA.2204@.TK2MSFTNGP10.phx.gbl...
> Running on Sql Server 2000.
> I was curious if there is a bug in DATEDIFF when dealing with years. The
> months seem to work correctly, but the Years are all over the place.
> Here is some examples:
> select
> DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
> select
> DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
> select
> DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
> -- --
> 1 6 Should be 0 and 6
> (1 row(s) affected)
>
> -- --
> 1 15 Should be 0 and 15
> (1 row(s) affected)
>
> -- --
> 2 18 Should be 1 and 18
> (1 row(s) affected)
> Am I missing something?
> Thanks,
> Tom
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:B4D02445-41D7-43CC-B570-DF88B181FF3C@.microsoft.com...
> There is no bug, it is working as it was meant to. All it does, I
> believe,
> is take the year into account, does not do anything clever with the
> respective position within each year.
If that is the case, it is relatively useless.
I am not actually passing the literal date as shown, but 2 smalldatetime
fields from my table.
But 6 months does not equal a year and 18 months doesn't equal a 2 years.
If what you are saying is true about just looking at the year, why then does
not the MONTH calculation look only at the months. It does calculate
correctly.
Here is a calculation that is 7 days apart, so months should be 0 and it is:
select
DATEDIFF(YEAR,'11/01/99','11/05/99'),DATEDIFF(MONTH,'11/01/99','11/08/99')
-- --
0 0
(1 row(s) affected)
> "tshad" wrote:
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uLu6kFVFGHA.3120@.TK2MSFTNGP10.phx.gbl...
> (a) never use such ambiguous date formats. I have no idea whether that
> first date is November 1 2004, January 11 2004, January 4th 1911, or
> January 4th 2011. The server you are on is maybe guessing right, but what
> if you run this code on a different server with different SET DATEFORMAT
> or regionl settings?
>
I am not using ambiguous formats. This was just for demonstration. I am
actually using fields from my table:
DATEDIFF(YEAR,FromDate,ToDate) (FromDate and ToDate are DateTime
fields)
> (b) DATEDIFF(YEAR, measures the number of years that have passed. So,
> DATEDIFF(YEAR, '20051231','20060101') = 1
> DATEDIFF(YEAR, '20050101','20061231') = 1
> This result is expected, and is not a bug. Basically, you can pretend
> that it strips the month/day parts from the date (and time if that is
> supplied as well), and just subtracts the integer derived from
> DATEPART(YEAR, <dateValue> ).
>
If not a bug, definately makes no sense to me as to what usefulness it would
be.
> If you're trying to calculate age based on birthdays, it's a little more
> complex than a simple DATEDIFF.
> http://www.aspfaq.com/2233
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e5bgY%23UFGHA.2204@.TK2MSFTNGP10.phx.gbl...
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWHP%23HVFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> Yes, you are assuming DATEDIFF function on two dates returns the completed
> year, month etc, which is not accurate.
> DATEDIFF simply returns the number of date and time boundaries crossed
> between two specified dates. And the results you got are consistent.
No.
I would agree if MONTH worked the same way, but as I mentioned in another
response, but MONTH does work correctly.
What you should get is 0 years for 0-11 months and 1 for 1-12 months.
Subtracting the years makes no sense - at least as far as I can fathom.
Thanks,
Tom
> --
> Anith
>|||> But 6 months does not equal a year and 18 months doesn't equal a 2 years.
If you're interested in the number of months that have passed, then use
MONTH instead of YEAR as the argument to DATEDIFF.
> Here is a calculation that is 7 days apart, so months should be 0 and it
> is:
> select
> DATEDIFF(YEAR,'11/01/99','11/05/99'),DATEDIFF(MONTH,'11/01/99','11/08/99')
I'm not sure I understand your argument. Of course both return 0 -- you
have not crossed into a new month, nor have you crossed into a new year.
How about :
SELECT DATEDIFF(MONTH, '19991031', '19991101');
SELECT DATEDIFF(MONTH, '19991001', '19991130');
SELECT DATEDIFF(MONTH, '19991231', '20000101');
Gee, all of those return 1. In the first case, only 1 day has passed; in
the second case, almost 2 months have passed; and, in the third case, we've
crossed into a new year.
Should SQL Server guess what each one of us would consider a threshold for
the difference in those dates being anything other than 1 month? Do you
round up starting at 15 days, 28 days, 30 days, 31 days? What if it's
February and you're crossing a leap year? What about Daylight Savings Time?
What if the groundhog does't see his shadow? DATEDIFF is based on the
simple parts of a date, it is not a mind reader.|||(2005 - 2004) = 1
(2004-2005) = -1
(2005 - 2005) = 0
It is just working with the integer year numbers, no fancy date
calculations. What are you actually needing?
William Stacey [MVP]
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23pD85lVFGHA.216@.TK2MSFTNGP15.phx.gbl...
> My mistake on the MONTH ( I was actually using Days in my example on
> Months)
> But I see no use for the YEAR DATEDIFF ( I am sure there is some use, but
> to say 1 month and 15 months are both 1 year makes no sense)
> Tom
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:%232DO9JVFGHA.1288@.TK2MSFTNGP09.phx.gbl...
>
