HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Are SQL Server's Date values stored as a single int?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedserveraresqldatesinglevaluesint

Problem

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?

Solution

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?

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

Code Snippets

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

PagePID iam_chain_type PageType
------- -------------- --------
229     In-row data    10
228     In-row data    1
DBCC TRACEON(3604, -1);
DBCC PAGE(13, 1, 228, 3);
SELECT DATEDIFF(DAY, CONVERT(DATE, '0001-01-01'), CONVERT(DATE, '2012-06-11'));
SELECT CONVERT(VARBINARY(8), 734664);

Context

StackExchange Database Administrators Q#19204, answer score: 11

Revisions (0)

No revisions yet.