Fun Data Arithmetic

Question

Fun Data Arithmetic

Steve Jones - SSC Editor

SSC Guru

Points: 736291
More actions
October 30, 2017 at 10:53 pm

#77609

Comments posted to this topic are about the item Fun Data Arithmetic

Viewing 12 posts - 1 through 11 (of 11 total)

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

Stewart "Arturius" Campbell SSC Guru Points: 72629 More actions · Answer 1

Nice one, thanks Steve

SQL Server stores these as integers, so the values are converted to integers, added, and then cast back to datetime.
Strange values for a strange holiday.

Actually, not so strange...
DATETIME has "1900-01-01 00:00:000" as a starting point (stored as 0).
therefore, 2000-01-01 - 1900-01-01 = 100 years.
The rest, as they say, is history...😎

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

ildjarn.is.dead Ten Centuries Points: 1164 More actions · Answer 2

ildjarn.is.dead

Ten Centuries

Points: 1164

October 31, 2017 at 1:12 am

#1965897

I think it stores the dates as floats, not ints.

HappyGeek SSCoach Points: 18729 More actions · Answer 3

HappyGeek

SSCoach

Points: 18729

October 31, 2017 at 1:50 am

#1965905

Good question thanks Steve.

...

Gareth Davison Right there with Babe Points: 723 More actions · Answer 4

ildjarn.is.dead - Tuesday, October 31, 2017 1:12 AM
I think it stores the dates as floats, not ints.

According to the documentation datetime is:

Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0â€“59.

I agree, that although there is a logical explanation it is strange behaviour. Behaviour that has been fixed with the DATETIME2 data type. Using this would type instead would yield a sensible error of:

Operand data type datetime2 is invalid for add operator.

George Vobr SSChampion Points: 10000 More actions · Answer 5

Thanks for this strange question. I had a bit of fun with this code:
DECLARE @dt1 DATETIME = '2017-10-31', @dt2 DATETIME = '2000-01-01', @dt_def DATETIME = '1900-01-01' -- DATETIME default value;

SELECT DATEADD(dd, DATEDIFF(dd, @dt_def, @dt1) + DATEDIFF(dd, @dt_def, @dt2), @dt_def);

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

ildjarn.is.dead - Tuesday, October 31, 2017 1:12 AM
I think it stores the dates as floats, not ints.

I used to think so as well but, with the help of a friend on another site, I proved that what Books Online days about 2 integers being used to store a date/time is absolutely spot on. Also, the integer that stores time stores the number of 1/300ths of a second since midnight of the given day and that's why the DATETIME datatype has a resolution of 3.3ms and is rounded during display to the nearest ms, which also means the last digit of ms will always be 0, 3, or 7.

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

GD_515 - Tuesday, October 31, 2017 2:10 AM
ildjarn.is.dead - Tuesday, October 31, 2017 1:12 AM
I think it stores the dates as floats, not ints.
According to the documentation datetime is:
Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0â€“59.
I agree, that although there is a logical explanation it is strange behaviour. Behaviour that has been fixed with the DATETIME2 data type. Using this would type instead would yield a sensible error of:
Operand data type datetime2 is invalid for add operator.

DATETIME2 fixed nothing and I avoid it like the plague because it's actually broken. It no longer allows direct temporal math and now violates the ANSI requirements of StartDate+Interval = EndDate and EndDate-StartDate = Interval.

Microsoft was forced into inadvertently admitting that they had made a huge mistake by coming out with DATEDIFF_BIG to make up for the mistake instead of fixing the real problem.

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

Gareth Davison Right there with Babe Points: 723 More actions · Answer 8

Jeff Moden - Tuesday, October 31, 2017 8:54 AM
DATETIME2 fixed nothing and I avoid it like the plague because it's actually broken. It no longer allows direct temporal math and now violates the ANSI requirements of StartDate+Interval = EndDate and EndDate-StartDate = Interval.

While there may be issues with DATETIME2, such as requiring an explicit DATEADD or DATEDIFF, for the scenarios described, I disagree that preventing someone adding two dates together is not a fix.

2017-10-31 + 2000-01-01 doesn't equal 2117-10-31 any more than Tuesday + Tuesday = Thursday.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 9

GD_515 - Tuesday, October 31, 2017 9:20 AM
Jeff Moden - Tuesday, October 31, 2017 8:54 AM
DATETIME2 fixed nothing and I avoid it like the plague because it's actually broken. It no longer allows direct temporal math and now violates the ANSI requirements of StartDate+Interval = EndDate and EndDate-StartDate = Interval.
While there may be issues with DATETIME2, such as requiring an explicit DATEADD or DATEDIFF, for the scenarios described, I disagree that preventing someone adding two dates together is not a fix.
2017-10-31 + 2000-01-01 doesn't equal 2117-10-31 any more than Tuesday + Tuesday = Thursday.

I do agree that adding two things that are obviously dates together may seem like a pitfall but limiting that would defeat the ability to do StartDate+Interval where the only current method of using a literal is to represent Interval is to list it as a date/time with an understand of what the offset from 1900-01-01 represents.

That being said, I will agree to disagree with you. 😉

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Having the ability to add an integer to a datetime works to the standard of StartDate + Interval. The datetime2 type doesn't allow this simple operation. The engine also does some other neat things with datetime under the hood to protect us from ourselves. In spite of it's limitation of 1753, it works for most scenarios. I'll stick with datetime because it just works.

The real question is about the date data type. Why doesn't StartDate + Interval work with a date? It should simply add a number of days to StartDate and return a date, but doesn't.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Ed Wagner - Wednesday, November 1, 2017 6:43 AM
Having the ability to add an integer to a datetime works to the standard of StartDate + Interval. The datetime2 type doesn't allow this simple operation. The engine also does some other neat things with datetime under the hood to protect us from ourselves. In spite of it's limitation of 1753, it works for most scenarios. I'll stick with datetime because it just works.
The real question is about the date data type. Why doesn't StartDate + Interval work with a date? It should simply add a number of days to StartDate and return a date, but doesn't.

th only reason to use datetime2 is if either dates outside the range of datetime or precision to better than 3.3ms are needed. I've done some computng where the better precision was needed, and although that work didn't need a relational database (it was mathematical modelling of software performance in very high speed data communications systems) I can easily envisage wanting that precision in RDBMS. But I have never needed dates earlier that 1753 or later than 9999 in computing, and don't imagine there'll be a real need in the next couple of millennia. And I most certainly have needed interval handling in databases. It's a blasted disgrace that none of the shiny new datetime2 and date and time types provide any tidy handling of intervals, a distinct step backward from datetime.

Tom