Exploring SQL Server 2008's New Date/Time Data Types
time and datetime2

Originally Published on SQL PASS's Technical Articles Site

Access the Article's Code

Introduction

SQL Server 2008 introduced four new data types related to date and time: date, time, datetime2, and datetimeoffset. In this first installation, we cover time and datetime2. In the follow-up, we will cover date and datetimeoffset.

For a Books Online introduction, see "Date and Time Data Types and Functions (Transact-SQL)". We will not cover the basics here. Note that the code and results here are based on SQL Server 2008 RTM.

It is important to be aware that time, datetime2, and datetimeoffset all have variable precision (0 to 7). Date does not.

Let's look at these new data types using code to see how they work with some of our favorite date/time-related functions and a couple of new functions. Most of the results are as-expected; a few are surprising.

time

Code like this

DECLARE @t_0a time(0)
DECLARE @t_0b time(0)

SET @t_0a = GETDATE()
SET @t_0b = SYSDATETIME()

SELECT
    @t_0a             AS 'GETDATE()',
    @t_0b             AS 'SYSDATETIME()',
    DATALENGTH(@t_0b) AS 'DataLength'

creates the results in the following table.

Data Type: time

Precision GETDATE() SYSDATETIME() DataLength
0 09:31:39 09:31:39 3
1 09:31:38.6 09:31:38.6 3
2 09:31:38.57 09:31:38.57 3
3 09:31:38.567 09:31:38.567 4
4 09:31:38.5670 09:31:38.5671 4
5 09:31:38.56700 09:31:38.56705 5
6 09:31:38.567000 09:31:38.567051 5
7 09:31:38.5670000 09:31:38.5670514 5
unspecified* 09:31:38.5670000 09:31:38.5670514 5
Reference
smalldatetime 2008-12-19 09:32:00 2008-12-19 09:32:00 4
datetime 2008-12-19 09:31:38.567 2008-12-19 09:31:38.567 8

* Declaring a variable or column as time with no precision defaults the precision to 7.

datetime2

Code like this

DECLARE @dt2_0a datetime2(0)
DECLARE @dt2_0b datetime2(0)

SET @dt2_0a = GETDATE()
SET @dt2_0b = SYSDATETIME()

SELECT
    @dt2_0a             AS 'GETDATE()',
    @dt2_0b             AS 'SYSDATETIME()',
    DATALENGTH(@dt2_0b) AS 'DataLength'

creates the results in the following table.

Data Type: datetime2

 Precision GETDATE() SYSDATETIME() DataLength
0 2008-12-19 09:31:39 2008-12-19 09:31:39 6
1 2008-12-19 09:31:38.6 2008-12-19 09:31:38.6 6
2 2008-12-19 09:31:38.57 2008-12-19 09:31:38.57 6
3 2008-12-19 09:31:38.567 2008-12-19 09:31:38.567 7
4 2008-12-19 09:31:38.5670  2008-12-19 09:31:38.5671 7
5  2008-12-19 09:31:38.56700 2008-12-19 09:31:38.56705 8
6  2008-12-19 09:31:38.567000 2008-12-19 09:31:38.567051 8
7  2008-12-19 09:31:38.5670000 2008-12-19 09:31:38.5670514 8
unspecified*  2008-12-19 09:31:38.5670000 2008-12-19 09:31:38.5670514 8
Reference
smalldatetime 2008-12-19 09:32:00 2008-12-19 09:32:00 4
datetime 2008-12-19 09:31:38.567 2008-12-19 09:31:38.567 8

* Declaring a variable or column as datetime2 with no precision defaults the precision to 7.

Who Stole My Nanoseconds? Default "SELECT" Value for datetime2

Generally, SELECTing a value gives us the column or variable's full detail. In the case of datatime2(7), the value is truncated. This code

DECLARE @dt  datetime
DECLARE @dt2 datetime2(7)

SET @dt  = SYSDATETIME()
SET @dt2 = SYSDATETIME()

SELECT
    @dt                             AS 'Default',
    CONVERT(varchar(27), @dt, 121)  AS 'Converted (121)'

SELECT
    @dt2                            AS 'Default',
    @dt2                            AS 'Default with a wide label..',
    CONVERT(varchar(27), @dt2, 121) AS 'Converted (121)'

produces this result:

Default                 Converted (121)
----------------------- ---------------------------
2008-12-19 14:19:39.797 2008-12-19 14:19:39.797

Default                Default with a wide label.. Converted (121)
---------------------- --------------------------- ---------------------------
2008-12-19 14:19:39.79 2008-12-19 14:19:39.7981074 2008-12-19 14:19:39.7981074

First, the default output for datetime2 truncates the values' last five digits. Second (and even more odd), the default output is one character shorter than the default output for datetime.

Any precision above 2 will be truncated in the default output.

Implicit Conversion

Implicit conversion works as expected. Here are some tests and results.

DECLARE @t   time
DECLARE @dt2 datetime2
DECLARE @sdt smalldatetime
DECLARE @s   char(27)

SET @dt2 = SYSDATETIME()
SET @sdt = SYSDATETIME()

-- Implicit conversion from datetime2(7) to time(7) works as expected
SET @t = @dt2
SELECT @dt2 AS 'Source (datetime2(7)) ', @t AS 'Implicit conversion to time(7) from datetime2(7)'

-- Implicit conversion from smalldatetime to time(7) works as expected
SET @t = @sdt
SELECT @sdt AS 'Source (smalldatetime) ', @t AS 'Implicit conversion to time(7) from smalldatetime'

-- Implicit conversion from string to datetime2(7) works as expected
SET @s = '2008-12-17 15:32:42.5625123'
SET @dt2 = @s
SELECT @s AS 'Source String', @dt2 AS 'Variable (datetime2(7)) from string'

-- Converting a time variable to a date-and-time format works, showing only the time portion
SET @t = SYSDATETIME()
SELECT @t AS 'Source time', CONVERT(varchar, @t, 121) AS 'Converted time(7)'


Source (datetime2(7))       Implicit conversion to time(7) from datetime2(7)
--------------------------- ------------------------------------------------
2008-12-19 14:52:36.6788189 14:52:36.6788189

Source (smalldatetime)      Implicit conversion to time(7) from smalldatetime
--------------------------- -------------------------------------------------
2008-12-19 14:53:00         14:53:00.0000000

Source String               Variable (datetime2(7)) from string
--------------------------- -----------------------------------
2008-12-17 15:32:42.5625123 2008-12-17 15:32:42.5625123

Source time      Converted time(7)
---------------- ------------------------------
14:52:36.6788189 14:52:36.6788189

Adding Sub-Precision Nanoseconds

According to Books Online, datetime2 is accurate to 100 nanoseconds. Adding 0 to 49 nanoseconds to an existing value does not change the value. Adding 50 to 99 nanoseconds to an existing value does change the value.

DECLARE @dt2a datetime2
DECLARE @dt2b datetime2

SET @dt2b = SYSDATETIME()

-- Adding 49 nanoseconds does not change the resulting value
SET @dt2a = DATEADD(ns, 49, @dt2b)
SELECT
    @dt2b               AS 'Source ',
    DATEPART(ns, @dt2b) AS 'NanoSeconds',
    @dt2a               AS 'Result: DateAdd(ns, 49) ',
    DATEPART(ns, @dt2a) AS 'NanoSeconds'

-- Adding 50 nanoseconds does change the resulting value
SET @dt2a = DATEADD(ns, 50, @dt2b)
SELECT
    @dt2b               AS 'Source ',
    DATEPART(ns, @dt2b) AS 'NanoSeconds',
    @dt2a               AS 'Result: DateAdd(ns, 50) ',
    DATEPART(ns, @dt2a) AS 'NanoSeconds'


Source                      NanoSeconds Result: DateAdd(ns, 49)     NanoSeconds
--------------------------- ----------- --------------------------- -----------
2008-12-19 15:08:19.893700
4   893700400 2008-12-19 15:08:19.8937004   893700400

Source                      NanoSeconds Result: DateAdd(ns, 50)     NanoSeconds
--------------------------- ----------- --------------------------- -----------
2008-12-19 15:08:19.893700
4   893700400 2008-12-19 15:08:19.8937005   893700500

You can add 49 milliseconds multiple times to a variable, and the value will not change.

Creating "Over-Precise" time Values

Since the accuracy of time(7) values is 100 nanoseconds, we would expect to be able to create fully-precise values from strings. This works as expected.

Here, we will create a full-precision time(7) from a 16-character string, then add another character and see what happens.

DECLARE @s1 varchar(19)
DECLARE @s2 varchar(19)
DECLARE @s3 varchar(19)
DECLARE @s4 varchar(19)
DECLARE @t time(7)

SET @s1 = '11:59:59.9999999'
    -- Initial full-precision time(7)
SET @s2 = '11:59:59.99999999'   -- Over by 1
SET @s3 = '11:59:59.999999999'  -- Over by 2
SET @s4 = '11:59:59.9999999999' -- Over by 3

SET @t = @s1
SELECT
    LEN(@s1)         AS 'String Len',
    @s1              AS 'Source',
    @t               AS 'Result ',
    DATEPART(ns, @t) AS 'NanoSeconds'

SET @t = @s2
SELECT
    LEN(@s2)         AS 'String Len',
    @s2              AS 'Source',
    @t               AS 'Result ',
    DATEPART(ns, @t) AS 'NanoSeconds'

SET @t = @s3
SELECT
    LEN(@s3)         AS 'String Len',
    @s3              AS 'Source',
    @t               AS 'Result ',
    DATEPART(ns, @t) AS 'NanoSeconds'

SET @t = @s4
SELECT
    LEN(@s4)         AS 'String Len',
    @s4              AS 'Source',
    @t               AS 'Result ',
    DATEPART(ns, @t) AS 'NanoSeconds'


 String Len Source              Result           NanoSeconds
----------- ------------------- ---------------- -----------
         16 11:59:59.9999999    11:59:59.9999999   999999900

 String Len Source              Result           NanoSeconds
----------- ------------------- ---------------- -----------
         17 11:59:59.99999999   12:00:00.0000000           0

 String Len Source              Result           NanoSeconds
----------- ------------------- ---------------- -----------
         18 11:59:59.999999999  12:00:00.0000000           0

Msg 241, Level 16, State 1, Line 33
Conversion failed when converting date and/or time from character string.

Interesting results. First, in converting from an over-precise string to a time variable, the 10-nanosecond value is used to round up. The same is true for the second test with the 1-nanosecond value. However, when the string goes past nanoseconds, the conversion fails. So SQL Server 2008 can't store a value with accuracy below 100 nanoseconds, but if you pass it a string value with precision past 100 nanoseconds, the full value will be evaluated when populating the time value. This is consistent with the results of the DATEADD function above.

Conclusion

Now that these new types are available, spend some time exploring them. Use the attached code samples to validate what is in the text of this article. Next time, we will dig into date and datetimeoffset.

Access the Article's Code