Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server stores DATETIME internally as two INT values, as SQLDenis explains.

Is it correct (by logical extension) that DATE types (SQL Server 2008+) are stored as a single INT?

share|improve this question

migrated from stackoverflow.com Jun 13 '12 at 17:21

This question came from our site for professional and enthusiast programmers.

3  
A better question: why does this matter? –  cdhowie Jun 11 '12 at 15:14
7  
@cdhowie I'm curious how it's stored in the back-end. It if doesn't matter to you then feel free to click a different question? –  Matthew Jun 11 '12 at 15:16
1  
What I mean is, Microsoft is free to change how data types are stored at any time. I'm not sure what you could feasibly do with this information that wouldn't be awful. –  cdhowie Jun 11 '12 at 15:17
1  
@cdhowie knowing how a specific DB engine stores the data allows you to make educated guesses for capacity planning –  N West Jun 11 '12 at 15:18
1  
@NWest if you know that DATE always uses 3 bytes, why does it matter if this is a 3-byte int, a 3-byte string or a 3-byte unicorn? –  Aaron Bertrand Jun 11 '12 at 15:24
show 13 more comments

1 Answer 1

up vote 8 down vote accepted

Just for fun, let's see what's on the page.

CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.experiment(d DATE NOT NULL);
CREATE CLUSTERED INDEX x ON dbo.experiment(d);
INSERT dbo.experiment SELECT SYSDATETIME();

DBCC IND('floob', 'dbo.experiment', 1);

Partial results (will differ on your system a bit):

----
13

PagePID iam_chain_type PageType
------- -------------- --------
229     In-row data    10
228     In-row data    1

So let's look at page 228:

DBCC TRACEON(3604, -1);
DBCC PAGE(13, 1, 228, 3);

Partial results. We see that the value for d occupies 3 bytes. If we look at the memory dump for the row, we see a hex value for the internal storage component: c8350b02 - what could that be?

enter image description here

SELECT DATEDIFF(DAY, CONVERT(DATE, '0001-01-01'), CONVERT(DATE, '2012-06-11'));

Result:

734664

So:

SELECT CONVERT(VARBINARY(8), 734664);

Result:

0x000B35C8

(Looks amazingly similar to what's on the page, just with bits flipped in the opposite order?)

Then:

SELECT DATEADD(DAY, CONVERT(INT, 0x000B35C8), CONVERT(DATE, '0001-01-01'))

Result:

2012-06-11
share|improve this answer
1  
now that is nerdilicious +1 –  RThomas Jun 11 '12 at 16:09
    
+1 excellent exploration of the page data and CONVERT behavior~ –  Matthew Jun 11 '12 at 16:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.