Having trouble understanding the DateDiff command across year boundaries

Question

Having trouble understanding the DateDiff command across year boundaries

huston.dunlap

SSCommitted

Points: 1551
More actions
June 23, 2010 at 8:55 am

#223173

I've been recently been doing a lot of datediff commands, and reading the books online it notes a flaw in the function.
SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000') returns 1.
In this example, even though the timedifference is certainly not a year, because the year part of the date changes the function returns 1.
I ran this in a server 2008 box and I validated the results.
However, when I ran the exact same command in a 2005 box, it returned 0.
So I have two questions:
1) Is there a difference in this function between 2008 and 2005, or is it another configuration I should look for to determine the difference?
2) Is the flaw noted above only for very small time intervals, or is the problem such that I should be doing a datediff on each part of the time (year,day,hour, etc) in order to truely get the difference? Right now the function seems very lackluster in its core functionality.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Well, first of all, you can't run this exact same command in sql 2005... the microsecond precision is only available in the datetime2 datatype and other SQL 2008 new datatypes, and when running that command in sql 2005 you get a conversion error.

However, running this command:

SELECT DATEDIFF(year, '2005-12-31 23:59:59.997', '2006-01-01 00:00:00.000')

in both environments does return a 1.

Note that if to run in SQL 2005, you changed it to:

SELECT DATEDIFF(year, '2005-12-31 23:59:59.999', '2006-01-01 00:00:00.000')

(just removed the extra 9's) then that will round up to 2006-01-01, and the difference in years will be zero.

Note that this is NOT a flaw in the function; instead it is doing what it is designed to do, and that is to report the number of BOUNDARIES crossed, not the DIFFERENCE.

Since you started off with 2005, and ended with 2006, there is but one boundary crossed. What you are trying to get is the ELAPSED time, which is different. If you want elapsed time, you will have to calculate it yourself.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

huston.dunlap SSCommitted Points: 1551 More actions · Answer 2

Thank you for the answer. Sorry I got a little snide in that last post, wasn't the best day for me.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

huston.dunlap (6/23/2010)
Thank you for the answer. Sorry I got a little snide in that last post, wasn't the best day for me.

So let's ask the real question... do you need something that will give an "age" in years properly? If so...

DECLARE @Birthday DATETIME, @DateToCheck DATETIME

SELECT @Birthday = '2005-12-31 23:59:59.997', @DateToCheck = '2006-01-01 00:00:00.000'

SELECT YEAR(@DateToCheck - DATEPART(dy, @Birthday) + 1) - YEAR(@Birthday)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)