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:

Specified scale

Result (precision, scale)

Column length (bytes)

Fractional seconds precision

datetime2

(27,7)

8

7

datetime2 (0)

(19,0)

6

0-2

datetime2 (1)

(21,1)

6

0-2

datetime2 (2)

(22,2)

6

0-2

datetime2 (3)

(23,3)

7

3-4

datetime2 (4)

(24,4)

7

3-4

datetime2 (5)

(25,5)

8

5-7

datetime2 (6)

(26,6)

8

5-7

datetime2 (7)

(27,7)

8

5-7

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:

Specified scale

Result (precision, scale)

Column length (bytes)

Fractional seconds precision

time

(16,7)

5

7

time(0)

(8,0)

3

0-2

time(1)

(10,1)

3

0-2

time(2)

(11,2)

3

0-2

time(3)

(12,3)

4

3-4

time(4)

(13,4)

4

3-4

time(5)

(14,5)

5

5-7

time(6)

(15,6)

5

5-7

time(7)

(16,7)

5

5-7

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:

Specified scale

Result (precision, scale)

Column length (bytes)

Fractional seconds precision

datetimeoffset

(34,7)

10

7

datetimeoffset(0)

(26,0)

8

0-2

datetimeoffset(1)

(28,1)

8

0-2

datetimeoffset(2)

(29,2)

8

0-2

datetimeoffset(3)

(30,3)

9

3-4

datetimeoffset(4)

(31,4)

9

3-4

datetimeoffset(5)

(32,5)

10

5-7

datetimeoffset(6)

(33,6)

10

5-7

datetimeoffset(7)

(34,7)

10

5-7

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

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles