SQL Server Inner Storage of datetime Data Type.

[Emtucifor]

SQL Server DOES store the datetime data type as two integers.

The first integer, the date, is the number of days from the "zero date".

tsqlLine number Off | Hide | Select all

1. SELECT CONVERT(DATETIME, 0)
2. -- 1900-01-01 00:00:00.000

The second integer, the time, is the number of ticks since midnight, where each tick represents 1/300th of a second. There are 24*60*60*300 or 25920000 of these in a day.

Converting values in SQL Server to varbinary does a direct conversion without "unpacking" the data. So converting to varbinary will let us see the internal representation of the value (try it on float or decimal for some fun thinking about how the complex packed data types work).

The following code will prove that SQL Server does indeed use two integers for datetime:

tsqlLine number Off | Hide | Select all

1. DECLARE @DATETIME DATETIME
2. SET @DATETIME = '20091026 23:59:59.997'
3. SELECT CONVERT(VARBINARY(8), @DATETIME)
4. SELECT CONVERT(INT, 0x00009CAE), CONVERT(INT, 0x018B81FF)
5. SELECT DATEADD(dd, 40110, DATEADD(ms, 25919999 * 10 / 3, 0))
6. -- 2009-10-26 23:59:59.997

It actually isn't reasonable to expect to convert back and forth between datetime and decimal (or even float) with no data loss. Why would you expect to convert from a fraction with a numerator of other than 10, 5, or 2 to a decimal data type and then always accurately be able to convert back to the original fraction's integers?

Using I1 and I2 as our two integers, to convert to decimal or float or any other numeric type N, SQL Server has to do something like this:

N = I1 + I2/25920000

Then, to convert back, it has to do something like this:

I1 = floor(N)
I2 = Round((N - floor(N)) * 25920000)

I hope this shows how subtle rounding issues can be introduced that could change the original date into something a little different.

The reason float does a better job here than decimal is probably because (I *think*) it can use other numerators besides 10 when storing numbers.

And finally, just to disarm any would-be debunkers:

tsqlLine number Off | Hide | Select all

1. SELECT CONVERT(DECIMAL(38, 37), 1)/300, CONVERT(FLOAT, 1)/300

This does not prove that decimal is more accurate than float, because you are viewing the unpacked decimal value, not getting any idea how that value was stored internally.

Right now I'm not able to find resources online to explain the internal datatype representation of float and numeric in SQL Server. But I'm really curious... maybe I'll research it. I feel a potential blog post coming over me...

[SQLDenis]

E, have you seen this blogpost by yours truly? How Are Dates Stored In SQL Server?

[Emtucifor]

Yes but I posted this here for a specific reason.

[SQLDenis]

There is a significant number of people that think that datetime is stored as floats...I guess this is because the conversion seems to work

I once went into this with a person on the msdn forum...and I could not convince him by pointing to BOL or showing him code that it was indeed 2 ints


well guess what a float is 4 bytes 2 ints are 8 bytes...datetime = also 8 bytes

[JeffModen]

Dennis... you need to check Books Online. The REAL datatype is 4 bytes... the number of bytes used by FLOAT is variable depending on the "n" of FLOAT(n). For values of N 1-24, it will consume 4 bytes. For values of N 25-53, it will consume 8 bytes... just like DATETIME.

You guys can say and do what you want... the FLOAT conversions work and the DECIMAL conversions work only as well as they would against a FLOAT data type. If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out. Just don't ever tell anyone to do fractional Decimal math against datetimes because it will eventually fail you. It's gotta be FLOAT instead.

Heh... of course, we all use date functions for that, right? (Not)

[SQLDenis]

Dennis... you need to check Books Online. The REAL datatype is 4 bytes... the number of bytes used by FLOAT is variable depending on the "n" of FLOAT(n). For values of N 1-24, it will consume 4 bytes. For values of N 25-53, it will consume 8 bytes... just like DATETIME.

You guys can say and do what you want... the FLOAT conversions work and the DECIMAL conversions work only as well as they would against a FLOAT data type. If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out. Just don't ever tell anyone to do fractional Decimal math against datetimes because it will eventually fail you. It's gotta be FLOAT instead.

Heh... of course, we all use date functions for that, right? (Not)

from books on line: http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx

Values with the datetime data type are stored internally by Microsoft SQL Server 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 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.

[Emtucifor]

Dennis... you need to check Books Online. The REAL datatype is 4 bytes... the number of bytes used by FLOAT is variable depending on the "n" of FLOAT(n). For values of N 1-24, it will consume 4 bytes. For values of N 25-53, it will consume 8 bytes... just like DATETIME.

You guys can say and do what you want... the FLOAT conversions work and the DECIMAL conversions work only as well as they would against a FLOAT data type. If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out. Just don't ever tell anyone to do fractional Decimal math against datetimes because it will eventually fail you. It's gotta be FLOAT instead.

Heh... of course, we all use date functions for that, right? (Not)

I almost cannot believe that after the proofs I presented you're going to go on believing that datetime is stored as float (!?!?!). I'm totally floored. I guess I'll have to lower my expectations. Seriously, this is almost inexplicable for a person who otherwise seems to have a head on his shoulders.

[Emtucifor]

Jeff,

Here is the definitive proof that the datetime data type is stored in SQL Server as two four-byte integers, just as Books Online claims.

First, this script creates some tables with a column 'sandwiched' between two character columns so we can really easily find the bytes for our desired value (and we don't have to get into all the complex details of how the data is stored in the page). Then it uses the DBCC PAGE command to output the raw page data for those three tables.

tsqlLine number Off | Hide | Select all

1. CREATE TABLE DtExam (a CHAR (2) not null, d DATETIME not null, b CHAR(2) not null PRIMARY KEY CLUSTERED (a, d, b))
2. INSERT DtExam VALUES ('ab', 0, 'cd')
3. INSERT DtExam VALUES ('ab', '20091204 14:41:00', 'cd')
4.  
5. CREATE TABLE FltExam (a CHAR (2) not null, f FLOAT not null, b CHAR(2) not null PRIMARY KEY CLUSTERED (a, f, b))
6. INSERT FltExam VALUES ('ab', CONVERT(FLOAT, CONVERT(DATETIME, 0)), 'cd')
7. INSERT FltExam VALUES ('ab', CONVERT(FLOAT, CONVERT(DATETIME, '20091204 14:41:00')), 'cd')
8.  
9. CREATE TABLE IntExam (a CHAR (2) not null, timeint INT not null, dayint INT not null, b CHAR(2) not null PRIMARY KEY CLUSTERED (a, timeint, dayint, b))
10. INSERT IntExam VALUES ('ab', 0, 0, 'cd')
11. INSERT IntExam VALUES ('ab', DATEDIFF(ms, 0, '14:41:00') * 3 / 10, DATEDIFF(dd, 0, '20091204'), 'cd')
12.  
13. DBCC TRACEON(3604)
14.  
15. DECLARE @SQL VARCHAR(8000)
16. SELECT @SQL = 'DBCC PAGE(''' + DB_NAME() + ''', ' + CONVERT(VARCHAR(11), CONVERT(INT, SUBSTRING(FIRST,6,1) + SUBSTRING(FIRST,5,1))) + ', ' + CONVERT(VARCHAR(11), CONVERT(INT, SUBSTRING(FIRST,4,1) + SUBSTRING(FIRST,3,1) + SUBSTRING(FIRST,2,1) + SUBSTRING(FIRST,1,1))) + ', 1)'
17. FROM sysindexes WHERE id = OBJECT_ID('DtExam') AND indid = 1
18. DBCC TRACEON(3604)
19. EXEC (@SQL)
20.  
21. SELECT @SQL = 'DBCC PAGE(''' + DB_NAME() + ''', ' + CONVERT(VARCHAR(11), CONVERT(INT, SUBSTRING(FIRST,6,1) + SUBSTRING(FIRST,5,1))) + ', ' + CONVERT(VARCHAR(11), CONVERT(INT, SUBSTRING(FIRST,4,1) + SUBSTRING(FIRST,3,1) + SUBSTRING(FIRST,2,1) + SUBSTRING(FIRST,1,1))) + ', 1)'
22. FROM sysindexes WHERE id = OBJECT_ID('FltExam') AND indid = 1
23. EXEC (@SQL)
24.  
25. SELECT @SQL = 'DBCC PAGE(''' + DB_NAME() + ''', ' + CONVERT(VARCHAR(11), CONVERT(INT, SUBSTRING(FIRST,6,1) + SUBSTRING(FIRST,5,1))) + ', ' + CONVERT(VARCHAR(11), CONVERT(INT, SUBSTRING(FIRST,4,1) + SUBSTRING(FIRST,3,1) + SUBSTRING(FIRST,2,1) + SUBSTRING(FIRST,1,1))) + ', 1)'
26. FROM sysindexes WHERE id = OBJECT_ID('IntExam') AND indid = 1
27. EXEC (@SQL)

Second, confirm that the values listed below exactly match your own server's output. You're looking for the lines in the "DATA:" section, under Slot 0 and Slot 1, the hexadecimal and ascii output of each row's data for each table.

Once you're satisfied, then run the script below.

tsqlLine number Off | Hide | Select all

1. -- For all of these, the values come from the first hex output row (00000000:) for Slot 0 and 1
2. /* DtExam, datetime
3. 10001000 61620000 00000000 00006364 †....ab........cd
4. 10001000 616250f9 f100d59c 00006364 †....abP.......cd
5. 8 bytes for datetime: 50f9 f100d59c 0000 */
6. SELECT *, CONVERT(BINARY(4), REVERSE(CONVERT(BINARY(4), d))), CONVERT(BINARY(4), REVERSE(SUBSTRING(CONVERT(BINARY(8), d), 1, 4))) FROM DtExam
7. -- 0x50F9F100 0xD59C0000
8. --   50f9 f100d59c 0000
9.  
10. /* FltExam, float
11. 10001000 61620000 00000000 00006364 †....ab........cd
12. 10001000 6162943e e993b39a e3406364 †....ab.>.....@cd
13. 8 bytes for float: 943e e993b39a e340 */
14. SELECT *, CONVERT(BINARY(8), REVERSE(CONVERT(BINARY(8), f))) FROM FltExam
15. -- 0x943EE993B39AE340
16. --  943e e993b39a e340
17.  
18. /* IntExam, two integers
19. 10001000 61620000 00000000 00006364 †....ab........cd
20. 10001000 616250f9 f100d59c 00006364 †....abP.......cd
21. 4 bytes for int 1: 50f9 f100
22. 4 bytes for int 2: d59c 0000
23. */
24. SELECT *, CONVERT(BINARY(4), REVERSE(CONVERT(BINARY(4), timeint))), CONVERT(BINARY(4), REVERSE(CONVERT(BINARY(4), dayint))) FROM IntExam
25. -- 0x50F9F100 0xD59C0000
26. --   50f9 f100  d59c 0000
27.  
28. DROP TABLE DtExam
29. DROP TABLE FltExam
30. DROP TABLE IntExam

The hex values for each column of interest appear between "6162" on the left and "6364" on the right, the hex values for the ASCII characters 'ab' and 'cd'. You can confirm that the values for the DtExam table exactly match those from the IntExam table, but not the FltExam table. The rowsets that are returned are also interesting.

Note that everything is in reverse byte order so I had to do some reversing and converting to get directly comparable hex strings. You can remove my conversions to see the original hex values.

These scripts prove several things:

1. Datetime is stored as two 4-byte integers: the first is the number of 1/300th-of-a-second 'ticks' Since midnight; the second is the number of days since the anchor date of '19000101'.

2. Although the float data type can be stored in 8 bytes, that is just a coincidence. Datetime is not stored as a float.

3. Converting SQL Server data types to binary is a reliable way to see how it will store the data in pages (on disk). Converting to and from other data types doesn't prove anything about storage (though it may expose rounding or other behavior for numeric conversion).

For further reference on SQL Server's data page format, please see Deciphering a SQL Server Data Page, Data page structure and dbcc page by Nigel Rivett (I could only get a cached copy), and SQL Server Data Structure.

Jeff, are you convinced now?

Erik

[JeffModen]

I guess I'll have to lower my expectations. Seriously, this is almost inexplicable for a person who otherwise seems to have a head on his shoulders.

I very much appreciate your "proof"... but I guess I'll have to lower my expectations of you, as well. I can't believe that you're the type of person that would lower the conversation like that.

Heh... and you still haven't explained why the float conversions work and the decimal conversions don't. Have you ever considered that FLOAT...

Ah.. never mind. After your ad hominem comments, I don't care what you've considered.

[Emtucifor]

Jeff,

You're right, I probably didn't need to say that. My apologies.

I honestly thought you wouldn't mind my straightforward declaration, since I also was saying I was only disappointed because of my high expectations based on your demonstrated ability. But I fully agree I ought to avoid any kind of negative judgment if at all possible.

Have you considered that your own speech was not without objectionable content? ("If it helps you to think of it as two INTEGERs as MS suggests, then knock yourself out.") This was clearly belittling our intelligence in a condescending way.

Anyway, that you put proof in quotes and other reading between the lines leads me to wonder if you still doubt?

I haven't endeavored to explain why the float conversion works and the decimal conversion doesn't. Perhaps I'll try that soon.

In any case, I do understand the concept of not casting your pearls before swine. I also understand being too thin-skinned and loudly giving up a fight on a shallow pretext.

[JeffModen]

I also understand being too thin-skinned and loudly giving up a fight on a shallow pretext.

You just can't help it, can you?

And, yep... I apologize for the "knock yourself out" comment. Not giving up on the conversation... just giving up on it with you.

[JeffModen]

Ack... despite any personal conflicts, I just can't let good code go by. Nice job, Erik. So far as I'm concerned, it does in fact prove that the datetime datatype is stored as 2 integer values just as MS states and I stand corrected. Now to figure out why conversions don't treat it that way.

[chrissie1]

Ack... despite any personal conflicts, I just can't let good code go by. Nice job, Erik.

That, in my book makes you a good man. Perhaps even great.

[Emtucifor]

I also understand being too thin-skinned and loudly giving up a fight on a shallow pretext.

You just can't help it, can you?

And, yep... I apologize for the "knock yourself out" comment. Not giving up on the conversation... just giving up on it with you.

Sigh. I was still just trying to provoke you to keep the conversation going.

When I was young my older brother couldn't handle conflict of any kind. The moment there was strong disagreement, he'd leave the room or beg off for lame reasons. It used to infuriate me because we could never resolve anything and he would in essence "win by default". I guess that in this thread I was probably unconsciously reacting to him, not just to you. That doesn't excuse, but it does explain.

I also had experiences with him where he accused me of something that he was guilty of ("You're so *selfish*, Erik!"). He was mad that I wouldn't let him have the 4 chocolate cream filled doughnuts I'd bought, but only would let him have any of the 8 other doughnuts. So I was "selfish" for not giving him what he demanded... in that case I lost it and threw a Sega Genesis power supply at him. It missed him and went through the window. Of course, my response was totally out of proportion and way worse--he could have been seriously injured. Anyway, I was reacting to the "knock yourself out" comment the same way. Here was a person who wasn't listening to reason but at the same time insulting me for being unreasonable. I struggle with that.

The thing is, I value people and conflict spurs me to want to engage more, not disengage. I never leave an argument if I can help it, until the issue is resolved, no matter how heated it gets. My ultimate goal is always reconciliation (not just technologically or ideologically but also personally). I *try* to maintain equanimity and aplomb at all times but clearly (sadly) still fail. If you could find it in your heart to keep talking with me, I'd consider it a true gift.