Avoiding Unexpected Results with DATEDIFF in Year Comparisons

Question

Avoiding Unexpected Results with DATEDIFF in Year Comparisons

Viewing 15 posts - 16 through 30 (of 51 total)

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

SQLNoob91 Grasshopper Points: 23 More actions · Answer 1

Jeff Moden - Monday, April 9, 2018 11:31 AM
zechstarks - Monday, April 9, 2018 9:02 AM
You would actually want to divide by 365.25, not 365.0, if you want a more accurate year difference.
Gosh, no. Don't use division for this. The alternatives are easy and guaranteed to be accurate.

DATEDIFF(yy,@pLoDate,@pHiDate)
    - CASE
      WHEN CONVERT(CHAR(5),@pLoDate,1) > CONVERT(CHAR(5),@pHiDate,1)
      THEN 1
      ELSE 0
    END[/code]

Your calculation above is even more accurate - very nice! The code I was using: ROUND(CONVERT(INT,(DATEDIFF(DAY,AP.Birth_Date,CONVERT(DATE,CURRENT_TIMESTAMP))) / 365.25),-0) breaks right at the birthdate, where your solution does not.

Using 365.25 is still more accurate than the author's proposed 365.0 though 😉

DBA4482 SSC Journeyman Points: 77 More actions · Answer 2

I appreciate you all taking the time to read this article and provide feedback. Some very good points have been made, I apologize for the inaccuracies in the initial posting. I have submitted a revised version to avoid the issues from the original piece. Thank you again for the reviewing the article and your comments on this subject.

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

DBA4482 - Monday, April 9, 2018 2:17 PM
I appreciate you all taking the time to read this article and provide feedback. Some very good points have been made, I apologize for the inaccuracies in the initial posting. I have submitted a revised version to avoid the issues from the original piece. Thank you again for the reviewing the article and your comments on this subject.

Thumbs up to you for taking it all the right way!

--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)

PawJ SSC Enthusiast Points: 195 More actions · Answer 4

:exclamation::exclamation::exclamation:I withdraw this calculation... as it won't calculate correctly with only one year!!!:exclamation::exclamation::exclamation:

The simplest way I think
SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);

Some tests:

DECLARE @STARTDATE DATETIME = '2010-10-05'; DECLARE @TODAY DATETIME = '2018-10-05'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8

SET @TODAY = '2018-10-04'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 7

SET @TODAY = '2018-10-06'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8

kevin 19285 Valued Member Points: 63 More actions · Answer 5

PawJershauge @ Orifarm - Tuesday, April 10, 2018 12:15 AM
The simplest way I think
SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);
Some tests:
DECLARE @STARTDATE DATETIME = '2010-10-05'; DECLARE @TODAY DATETIME = '2018-10-05'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
SET @TODAY = '2018-10-04'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 7
SET @TODAY = '2018-10-06'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8

Try this with 1898-10-05 to 1902-10-05 ... returns 3 (reason: 1900 not a leap year) won't fail again until 2100 tho 🙂

PawJ SSC Enthusiast Points: 195 More actions · Answer 6

kevin 19285 - Tuesday, April 10, 2018 7:19 AM
PawJershauge @ Orifarm - Tuesday, April 10, 2018 12:15 AM
The simplest way I think
SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);
Some tests:
DECLARE @STARTDATE DATETIME = '2010-10-05'; DECLARE @TODAY DATETIME = '2018-10-05'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
SET @TODAY = '2018-10-04'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 7
SET @TODAY = '2018-10-06'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
Try this with 1898-10-05 to 1902-10-05 ... returns 3 (reason: 1900 not a leap year) won't fail again until 2400 tho 🙂

I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Paw Jershauge (Orifarm) - Tuesday, April 10, 2018 7:24 AM
kevin 19285 - Tuesday, April 10, 2018 7:19 AM
PawJershauge @ Orifarm - Tuesday, April 10, 2018 12:15 AM
The simplest way I think
SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);
Some tests:
DECLARE @STARTDATE DATETIME = '2010-10-05'; DECLARE @TODAY DATETIME = '2018-10-05'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
SET @TODAY = '2018-10-04'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 7
SET @TODAY = '2018-10-06'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
Try this with 1898-10-05 to 1902-10-05 ... returns 3 (reason: 1900 not a leap year) won't fail again until 2400 tho 🙂
I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:

Why use a calculation that has a chance of failure? Just because you don't use dates where it won't work now, are you sure that you will always be in such an environment? Wouldn't you rather use a method that is always sure to work? Or is it you are willing to use what ever works for now?

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 8

Paw Jershauge (Orifarm) - Tuesday, April 10, 2018 7:24 AM
I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:

I encounter this type of thinking occasionally. As someone who lived and worked through Y2K, I find it disappointing that we have learned so little. There will never be a date hack in any system that I have control of. Any developer who insists on a date hack is on my short list to fire.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 9

David Strack and Jeff Moden,

I have run your queries between 12 April 2016 and 10 April 2018 and get only 1 year which I do not think is correct so I changed the code a bit and got 2 years.
CASE WHEN @TODAY < DATEADD(YEAR, DATEDIFF(YEAR, @STARTDATE, @TODAY), @STARTDATE) THEN 0 ELSE 1 END
It was:
CASE WHEN @TODAY < DATEADD(YEAR, DATEDIFF(YEAR, @STARTDATE, @TODAY), @STARTDATE) THEN 1 ELSE 0 END
I haven't had time to test other dates. Please tell me what you think. I also have been to busy to go through the other comments so please don't shoot me if someone picked this up.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

PawJ SSC Enthusiast Points: 195 More actions · Answer 10

GeorgeCopeland - Tuesday, April 10, 2018 7:39 AM
Paw Jershauge (Orifarm) - Tuesday, April 10, 2018 7:24 AM
I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:
I encounter this type of thinking occasionally. As someone who lived and worked through Y2K, I find it disappointing that we have learned so little. There will never be a date hack in any system that I have control of. Any developer who insists on a date hack is on my short list to fire.

Well, working with financial data, i wont ever come to any dates before 1900 and certainly not date after 2200... so i don't see why i should be bothered, with failures outside my range... But if you think your program will survive 200 years from now.. be my guest at take ALL exceptions that may and may not occur into consideration. :rolleyes:

PawJ SSC Enthusiast Points: 195 More actions · Answer 11

Lynn Pettis - Tuesday, April 10, 2018 7:30 AM
Paw Jershauge (Orifarm) - Tuesday, April 10, 2018 7:24 AM
kevin 19285 - Tuesday, April 10, 2018 7:19 AM
PawJershauge @ Orifarm - Tuesday, April 10, 2018 12:15 AM
The simplest way I think
SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);
Some tests:
DECLARE @STARTDATE DATETIME = '2010-10-05'; DECLARE @TODAY DATETIME = '2018-10-05'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
SET @TODAY = '2018-10-04'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 7
SET @TODAY = '2018-10-06'; SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25); --Result: 8
Try this with 1898-10-05 to 1902-10-05 ... returns 3 (reason: 1900 not a leap year) won't fail again until 2400 tho 🙂
I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:
Why use a calculation that has a chance of failure? Just because you don't use dates where it won't work now, are you sure that you will always be in such an environment? Wouldn't you rather use a method that is always sure to work? Or is it you are willing to use what ever works for now?

My calculation wont hit that error, as i just wrote... i wont ever come to the range outside year 1900 - 2200.. so why should i be bothered with what could happen outside my range???

Thom A SSC Guru Points: 99133 More actions · Answer 12

Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:36 AM
My calculation wont hit that error, as i just wrote... i wont ever come to the range outside year 1900 - 2200.. so why should i be bothered with what could happen outside my range???

For the same reason the business I currently work for, in the 1970's, said "We'll never be still using that service in 2011, so it doesn't matter that that we set the upper date range to some odd date in 2010." It's 2018, that server is still there (in it's wooden case)... and yes, it did go wrong.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

PawJ SSC Enthusiast Points: 195 More actions · Answer 13

Thom A - Wednesday, April 11, 2018 1:46 AM
Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:36 AM
My calculation wont hit that error, as i just wrote... i wont ever come to the range outside year 1900 - 2200.. so why should i be bothered with what could happen outside my range???
For the same reason the business I currently work for, in the 1970's, said "We'll never be still using that service in 2011, so it doesn't matter that that we set the upper date range to some odd date in 2010." It's 2018, that server is still there (in it's wooden case)... and yes, it did go wrong.

You should upgrade... systems from 1970 are soooo out dated... OMG lol

Thom A SSC Guru Points: 99133 More actions · Answer 14

Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 1:50 AM
Thom A - Wednesday, April 11, 2018 1:46 AM
Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:36 AM
My calculation wont hit that error, as i just wrote... i wont ever come to the range outside year 1900 - 2200.. so why should i be bothered with what could happen outside my range???
For the same reason the business I currently work for, in the 1970's, said "We'll never be still using that service in 2011, so it doesn't matter that that we set the upper date range to some odd date in 2010." It's 2018, that server is still there (in it's wooden case)... and yes, it did go wrong.
You should upgrade... systems from 1970 are soooo out dated... OMG lol

I think my point has been missed...

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

PawJ SSC Enthusiast Points: 195 More actions · Answer 15

Thom A - Wednesday, April 11, 2018 2:05 AM
Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 1:50 AM
Thom A - Wednesday, April 11, 2018 1:46 AM
Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:36 AM
My calculation wont hit that error, as i just wrote... i wont ever come to the range outside year 1900 - 2200.. so why should i be bothered with what could happen outside my range???
For the same reason the business I currently work for, in the 1970's, said "We'll never be still using that service in 2011, so it doesn't matter that that we set the upper date range to some odd date in 2010." It's 2018, that server is still there (in it's wooden case)... and yes, it did go wrong.
You should upgrade... systems from 1970 are soooo out dated... OMG lol
I think my point has been missed...

I guess my point was missed as well... systems that are over 50 years old, should defently have be updated along the way, and should you under an upgrade find, that you would encounter such an calculation issue, which I highly doubt, then upgrade... there's absolutly NO need to take into account ranges that are not in your scope, regardless. THIS is not an storage issue, its a calculation issue which wont ever be effected by what I do, and I'm 10000000000 % sure that my program/calculation running on a SQL server wont be here in 200 years.... and the Y2K bug, was not a calculation issue, it was storage... (YY)