8

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?

0

1 Answer 1

11

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
2
  • Hmm but what about the time part?
    – Azimuth
    Sep 11, 2018 at 18:13
  • @Azimuth The question was about date which doesn't have a time part. Sep 11, 2018 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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