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:

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

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


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.

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

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


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

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.


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.

Related Articles

Handling SQL Server Errors (5 April 2010)

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server 2008: Table Valued Parameters (24 July 2008)

Using the TIME data type in SQL Server 2008 (6 March 2008)

Working with Time Spans and Durations in SQL Server (15 October 2007)

DATEDIFF Function Demystified (20 March 2007)

Other Recent Forum Posts

Backup up System databases only and excluding the User databases (16h)

Bulk Insert with variables and return specific columns (1d)

Hive-exec.jar manual update? (1d)

Excel Connection File name specified is invalid. File name cannot be an existing directory (1d)

SQL i/o device error (1d)

Best books to learn concepts of database fundamentals? (2d)

Ledger Opening is not carry forward on date filter (2d)

How to add each backups to it's own folder (2d)

- Advertisement -