SQL Server 2008 DATE data type

SQL Server 2008 has several new data types, including new date and time types.  In a series of short posts I’ll go into how these data types are structured. All of these new types are supported in SQL Internals Viewer, and a new data type viewer is coming up in a future version of the app.

The new date and time data types are:

• DATE – Stores a date value
• TIME – Stores a time value with an accuracy of up to 100 nanoseconds
• DATETIME2 – Stores a date and time value with the higher TIME accuracy
• DATETIMEOFFSET – Stores a date and time value with a time zone offset

DATE type internals

The date type simply stores a date, ranging from January 1st 0001 (1 AD) to December 31st 9999. Internally the type is stored as a 3 byte (24-bit) integer. The integer value is the number of days since the base date of 01/01/0001.

It isn’t possible to convert from an INT to DATE directly. Running SELECT CONVERT(DATE, 1) will result in the following error:

Msg 529, Level 16, State 2, Line 1

Explicit conversion from data type int to date is not allowed.

However it is possible to convert to from INT to DATE by converting first to BINARY(3), reversing the bytes, and converting to DATE. (I’ll explain why you need the REVERSE and CONVERT in a subsequent post.)

This shows each increment of the 24-bit integer represents a day from the base date:

DECLARE @IntValue INT

SET @IntValue = 0

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

 -- Result: 0001-01-01

SET @IntValue = 1

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

 -- Result: 0001-01-02

SET @IntValue = 2

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

 -- Result: 0001-01-03

SET @IntValue = 3

SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))

 -- Result: 0001-01-04

Unlike DATE and SMALLDATETIME it doesn’t seem possible (with the November CTP) to add and subtract days from a date:

DECLARE @Date DATE = '2008-02-01'

SELECT @Date + 1

This results in the error: 

Msg 206, Level 16, State 2, Line 2

Operand type clash: date is incompatible with int

Adding two dates together also results in the following error:

Msg 8117, Level 16, State 1, Line 3

Operand data type date is invalid for add operator.

Books Online has more information about the new DATE type here.

Next up, the new TIME type.