Using the DATE data type in SQL Server 2008

By Bill Graziano on 6 December 2007 | 6 Comments | 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.

Discuss this article: 6 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

INITCAP() IN SQL SERVER (1 Reply)

Req:Create insert & update trigger on a StoredProc (0 Replies)

INCORRECT SYNTAX NEAR 'INDEX'. sql 2008 (0 Replies)

Possibility : MSSQL to SQLite Bi-directional Sync (0 Replies)

migration oracle with XML to sql server (6 Replies)

Help Writing Stored Procedure (4 Replies)

if child exists with same location, take it, else (9 Replies)

Updating columns (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -