Using the DATE data type in SQL Server 2008

By Bill Graziano on 6 December 2007 | Tags: Data Types , SQL Server 2008 Features

In SQL Server 2008 we get a new DATE date type that allows you to store a date without a time.

The DATE data type is relatively easy to work with. A simple example is below:

DECLARE @d1 DATE
SELECT	@d1 = '2/21/2007'
SELECT	@d1 as [Date]

- - - - - - - - - - - - - - - - -

Date
----------
2007-02-21

Notice that by default the date is displayed in YYYY-MM-DD format.  The documentation in my build indicates that this is the default for the DATE data type.  The DATE data type can store values from 0001-01-01 through 9999-12-31.  The DATE data type seems to accept most of the formats that the DATETIME accepted.

The DATE data type only takes three bytes to store its values as compared to eight bytes for a DATETIME.

DECLARE @d1 DATE
SELECT	@d1 = '9999-12-31'
SELECT	DATALENGTH(@d1) as Date_Bytes

- - - - - - - - - - - - - - - - - - - - - -

Date_Bytes
-----------
3

The DATE data type will accept DATETIME values and implicitly convert it to a DATE by removing the time portion.

DECLARE @dt DATETIME
SELECT	@dt = '2007-05-13 23:22:12'
SELECT	@dt AS [DateTime],
		CAST(@dt AS DATE) AS [Date]

- - - - - - - - - - - - - - - - - - - - - -

DateTime                Date
----------------------- ----------
2007-05-13 23:22:12.000 2007-05-13

I compared a DATETIME indexed column to a DATE scalar value in the WHERE clause and SQL Server used an index seek.   At least it did after I put enough rows in the table.  If the DATETIME data type had a value for the time portion the match failed.  If it only had a date portion then it matched the row.

The major date functions DATEADD, DATEDIFF, YEAR, etc. all work with the DATE data type.

Summary

As you can see, the DATE data type isn't very complex.  Its small size should give it an advantage in indexes where you only need to index for the date.  It should be fairly easy to drop it in as a replacement for DATETIME.