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.


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

SQL Server Configuration Manager Not Usable (12h)

Select a single row based on conditions in multiple rows (1d)

I want Help Managing Big Data Sets in T SQL Efficiently (2d)

SQL stored procedure to load the error and correct record based on some business rules (2d)

Query is running too long (2d)

Sql Query to check status change of an item (3d)

Can I create differential backups tied to a specifc Full backup instead of the most recent? (8d)

My informix Sql query retruns Null always (9d)

- Advertisement -