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

CASE Statement EXISTS Query Check Assist (5h)

Managed Instance - Storage Blob Container Backup file Upload issue (18h)

Need help with nested list in SQL (2d)

Can't restore bak file from MS SQL Server 2000 (4d)

Connecting to SQL Server from externally with proxy server (4d)

How to Find if Java is Installed (4d)

How To Insert Title At Start of SQL Results and Between Another SQL Result Sets (4d)

Help with sql query find combinations that are not equal, please (5d)

- Advertisement -