New Date Data Types with SQL Server 2008

Have you ever wanted to store just a date without the time portion in your database? Or wanted a time value to have more precision? With the introduction of SQL Server 2008, Microsoft introduced some new date data types that will allow you to store only a date, have more precision for the time portion, and more. These new data types provided you with addition options for storing your date and time related information. In this article, I will be looking at these new date data types and comparing them to the existing data types that are available within older versions of SQL Server.

Review of Older Date Data Types

Before we start talking about the new SQL Server 2008 date data types let’s first review the two date data types that were available in SQL Server 2005, and older versions of SQL Server. These older data types are DATETIME, and SMALLDATETIME data types. These two data types are still available in SQL Server 2008.

The DATETIME data type stores both the date and the time portion together as single column value. This data type supported dates between January 1, 1753 and December 31, 9999, with the time portion being accurate to 3.33 milliseconds and required 8 bytes of storage space. The SMALLDATE data type requires less storage space then a DATETIME data type, only 4 bytes, but supports a smaller date and time range. SMALLDATE only supports dates from January 1, 1900 through June 6, 2079, and the time portion is only accurate down to the minute.

These limited date formats restricted your options for storing older dates, and time values with greater precision. These limitations made it difficult to support migrating data from other storage structures that supported different date and time ranges. With the new date and time data types of SQL Server 2008 you now have more options for storing date and time data.

DATETIME2

The DATETIME2 data type is an extension of the DATETIME data type. This new data type extends the range of acceptable dates and adds additional precisions to the time portion of the date/time value. The DATETIME2 column supports dates from 0001-01-01 through 9999-01-01. This expanded date range now allows you to store older dates without using some creative coding algorithm.

The time portion supports varying degrees of precisions depending on how you define the DATETIME2 column. The time portion can store a time value with only hours, minutes and seconds, or it can support storing fractional seconds at different precisions, up to seven decimal places. The fraction of seconds can be made to be accurate down to 100 nanoseconds.

Using the DATETIME2 data type you can store and display dates in varying lengths from 19 (YYYY-MM-DD hh:mm:ss) to 27 (YYYY-MM-DD hh:mm:ss.0000000) characters in length . Depending on the amount of precision stored determines the number of bytes of disk space it takes. To store different precisions with the DATETIME2 column you specify a length when defining a column. The precision is specified like DATETIME2(X), where X is the precision. X can be a value from 0 to 7. The display length and storage space associated with each precision setting is shown in the following table:

As you can see from this table, the DATETIME2 column the disk space requires to store a date/time ranges in size for 6 to 8 bytes depending on how much time precision you want to retain.

The DATETIME2(3) format is equivalent to the DATETIME format we are all are use to from older versions of SQL Server. Although using the DATETIME2(3) format does support time accuracy to 1 millisecond, where as older versions only supported accuracy down to 3.33 milliseconds. Note that the DATETIME(3) format also only requires 7 bytes of disk space, where as a DATETIME column uses 8 bytes. This now means you can store the same date and time values of prior releases of SQL Server and save disk space at the same time.

Using the DATETIME(7) format allows you to store dates with up to 100 nanoseconds of precisions. Even with this added precision, you cannot ensure that a time column in a table will be unique. It is still possible to store two different records in the same table with the same time even when using the DATETIME(7) format.

Here is an example of how using different DATETIME2 precision values will display different time formats.

DECLARE @D0 datetime2(0) = '2008-05-10 06:59:11.1234567';
DECLARE @D1 datetime2(1) = '2008-05-10 06:59:11.1234567';
DECLARE @D2 datetime2(2) = '2008-05-10 06:59:11.1234567';
DECLARE @D3 datetime2(3) = '2008-05-10 06:59:11.1234567';
DECLARE @D4 datetime2(4) = '2008-05-10 06:59:11.1234567';
DECLARE @D5 datetime2(5) = '2008-05-10 06:59:11.1234567';
DECLARE @D6 datetime2(6) = '2008-05-10 06:59:11.1234567';
DECLARE @D7 datetime2(7) = '2008-05-10 06:59:11.1234567';
PRINT @D0;
PRINT @D1;
PRINT @D2;
PRINT @D3;
PRINT @D4;
PRINT @D5;
PRINT @D6;
PRINT @D7;

When I run this code I get the following output:

2008-05-10 06:59:11
2008-05-10 06:59:11.1
2008-05-10 06:59:11.12
2008-05-10 06:59:11.123
2008-05-10 06:59:11.1235
2008-05-10 06:59:11.12346
2008-05-10 06:59:11.123457
2008-05-10 06:59:11.1234567

Here you can see that each precision displayed a different number of fractional seconds. Note that DATETIME(4), DATETIME(5) and DATETIME(6) performed some rounding when I set the default value on the DECLARE statement.

Using DATETIME2 will now allow you more options for storing a date and a time as a single column. Now if you only want a date and time stored down to the seconds you can use DATETIME(0) to accomplish this requirement without extra code and wasting valuable disk space.

DATE

Have you ever wanted to store just the date without the time? If so, you couldn’t do that with any of the old date data types. Now with SQL Server 2008 you can use the DATE data type. The DATE data type allows you to store just a date value. It support dates from 0001-01-01 to 9999-12-31. The disk space required to store a DATE data type takes only 3 bytes. If you only need to store a date and no time value, you can save 1 byte of space over the old SMALLDATETIME data type.

Here is an example that shows how the value of a DATE variable will be displayed:

SET NOCOUNT ON;
DECLARE @D DATE = '2001-03-1';
PRINT @D;
SELECT CONVERT(char(10),@D,120) DATEONLY, @D [DATE-N-TIME]; 

Here is the output of this script:

2001-03-01
DATEONLY   DATE-N-TIME
---------- -----------------------
2001-03-01 2001-03-01 00:00:00.000

Here you can see that the PRINT statement displays the date in YYYY-MM-DD format, but SELECT statement requires you to use a CONVERT function to return the DATE variable in YYYY-MM-DD format. As you can see if you only specify the DATE variable in the SELECT statement then the output also contains a time portion, with a midnight setting for the time. This functionality is consistent with all of the new date and time data types.

TIME

SQL Server 2008 now has a TIME data type. The TIME data type allows you to store just the time, without a date. This would be useful if you wanted to store information about a particular time that doesn’t relate to a specific date. A TIME data type is stored using 24-hour time, it is not time zone aware and supports up to 100 nanoseconds of accuracy. The TIME data type supports different precisions from 0 to 7 just like the DATETIME2 format. The space requirement varies from 3 to 5 depending on the precision. The following table shows the length of a TIME column based on precision:

Here is an example of how using different DATETIME2 precision values will display different time formats.

DECLARE @T0 time(0) = '16:59:11.1234567';
DECLARE @T1 time(1) = '16:59:11.1234567';
DECLARE @T2 time(2) = '16:59:11.1234567';
DECLARE @T3 time(3) = '16:59:11.1234567';
DECLARE @T4 time(4) = '16:59:11.1234567';
DECLARE @T5 time(5) = '16:59:11.1234567';
DECLARE @T6 time(6) = '16:59:11.1234567';
DECLARE @T7 time(7) = '16:59:11.1234567';
PRINT @T0;  
PRINT @T1;  
PRINT @T2;  
PRINT @T3;  
PRINT @T4;  
PRINT @T5;  
PRINT @T6;  
PRINT @T7;

When I run this code, I get the following output:

16:59:11
16:59:11.1
16:59:11.12
16:59:11.123
16:59:11.1235
16:59:11.12346
16:59:11.123457
16:59:11.1234567

Note that TIME(4), TIME(5), and TIME(6) rounds the time when truncating the precision to match the precision, this is consistent with the behavior we saw above with the DATETIME2 data type.

Storing only the time portion in a TIME data type column now allows you the luxury of having SQL Server validate time values to make sure they contain a valid time, and save space all at the same time.

DATETIMEOFFSET

The last new date data type is DATETIMEOFFSET data type. This data type allows you to store a date and time (base on 24 hour clock) that is time zone aware. The time portion can support up to 100 nanoseconds accuracy just like DATETIME2 and TIME data types. The DATETIMEOFFSET requires 8 to 10 bytes of disk storage space depending on the amount of precision you want for the time portion of a column, see the following chart for more information:

Time zone aware means a time zone identifier is stored with a DATETIMEOFFSET column. The time zone identification is represented by a [-|+] hh:mm designation. A valid time zone range is from -14:00 to +14:00, this value is added or subtracted from Coordinated Universal Time (UTC) to obtain the local time.

When converting non-time zone aware time values to DATETIMEOFFSET a time zone setting of +00:00 is used, for more information about time conversion see the “Using date and time” topic in Books Online.

More Date and Time Choices

With SQL Server 2008, you now have more data type choices you can use to store your date and time data. You can use the DATE data type to store just a date, or TIME data type to store only a time value. The time portion of these new data types now supports accuracy to 100 nanoseconds. If you need to store dates that tracks the time zone of your SQL Server then you can use the DATETIMEOFFSET data type. With these new date/time data types, you should be able to find a solution to help you store your date in the correct format without having to write lots of custom code.

» See All Articles by Columnist Gregory A. Larsen