Originally Published on SQL PASS's Technical Articles Site
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.
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.
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.
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 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
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.8937004 893700400 2008-12-19 15:08:19.8937004 893700400
Source NanoSeconds Result: DateAdd(ns, 50) NanoSeconds
--------------------------- ----------- --------------------------- -----------
2008-12-19 15:08:19.8937004 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.
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.
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.