SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME

In the previous post, we have examined the DATE data type introduced by SQL Server 2008. In this post we will examine another new data type: TIME.

TIME Data Type

The TIME data type, as the name indicates, stores a "time" value. The TIME data type is capable of supporting fractional precision up to 100 nanoseconds. For the purpose of easy understanding, we could say, a TIME value is what is left when you remove the DATE part from a DATETIME value.

You could initialize a TIME variable with a DATETIME value or with a String Literal and the TIME portion of the value will be copied to the TIME variable.

DECLARE @t TIME = GETDATE()
SELECT @t -- 09:35:34.0130000

OR

DECLARE @t TIME = '12:00:23.1234567'
SELECT @t -- 12:00:23.1234567

The system function GETDATE() returns a DATETIME value and hence the precision of the result is only around 3 milliseconds. SQL Server 2008 introduced another system function, SYSDATETIME(), that returns a higher precision (up to 100 nanoseconds).

DECLARE @t TIME = SYSDATETIME()
SELECT @t -- 09:35:34.0156250

Precision

Though the TIME data type supports precision up to 100 nanoseconds, you may not always need it. You can control the level of precision when you declare your TIME variable. When you declare your TIME variable, you can specify a level (0 to 7) which controls the precision required for the given variable. 0 gives the lowest level of precision and 7, the highest.

DECLARE
@t TIME = GETDATE(),
@t0 TIME(0) = GETDATE(),
@t1 TIME(1)= GETDATE(),
@t2 TIME(2)= GETDATE(),
@t3 TIME(3)= GETDATE(),
@t4 TIME(4)= GETDATE(),
@t5 TIME(5)= GETDATE(),
@t6 TIME(6)= GETDATE(),
@t7 TIME(7)= GETDATE()
SELECT
@t, -- 11:30:38.0300000
@t1, -- 11:30:38.0
@t2, -- 11:30:38.03
@t3, -- 11:30:38.030
@t4, -- 11:30:38.0300
@t5, -- 11:30:38.03000
@t6, -- 11:30:38.030000
@t7 -- 11:30:38.0300000

As mentioned earlier, GETDATE() does not provide a higher level of precision as supported by the TIME data type. To see the precision of the TIME data type in action, let us run the above example with SYSDATETIME() function.

DECLARE
@t TIME = SYSDATETIME(),
@t0 TIME(0) = SYSDATETIME(),
@t1 TIME(1)= SYSDATETIME(),
@t2 TIME(2)= SYSDATETIME(),
@t3 TIME(3)= SYSDATETIME(),
@t4 TIME(4)= SYSDATETIME(),
@t5 TIME(5)= SYSDATETIME(),
@t6 TIME(6)= SYSDATETIME(),
@t7 TIME(7)= SYSDATETIME()
SELECT
@t, -- 11:35:53.3593750
@t1, -- 11:35:53.4
@t2, -- 11:35:53.36
@t3, -- 11:35:53.359
@t4, -- 11:35:53.3594
@t5, -- 11:35:53.35938
@t6, -- 11:35:53.359375
@t7 -- 11:35:53.3593750

The TIME data type maps with the TIMESPAN data type of ADO.NET. So your stored procedures or parameterized queries that accepts TIME data types can take a .NET TIMESPAN value.

The popular DATEPART() function will work with the TIME data type too. There are a few new flags added to the function.

DECLARE @t TIME
SELECT @t = SYSDATETIME()
SELECT
@t, -- 11:52:17.1406250
DATEPART(HOUR, @t), -- 11
DATEPART(HH, @t), -- 11
DATEPART(MINUTE, @t), -- 52
DATEPART(MI, @t), -- 52
DATEPART(N, @t), -- 52
DATEPART(SECOND, @t), -- 17
DATEPART(SS, @t), -- 17
DATEPART(S, @t), -- 17
DATEPART(MILLISECOND, @t), -- 140
DATEPART(MS, @t), -- 140
DATEPART(MICROSECOND, @t), -- 140625
DATEPART(MCS, @t), -- 140625
DATEPART(NANOSECOND, @t), -- 140625000
DATEPART(NS, @t) -- 140625000

The same set of flags work with the DATEADD() function too.

DECLARE @t TIME
SELECT @t = SYSDATETIME()
SELECT
@t, -- 11:58:49.9843750
DATEADD(HOUR, 5, @t), -- 16:58:49.9843750
DATEADD(HH, 5, @t), -- 16:58:49.9843750
DATEADD(MINUTE, 5, @t), -- 12:03:49.9843750
DATEADD(MI, 5, @t), -- 12:03:49.9843750
DATEADD(N, 5, @t), -- 12:03:49.9843750
DATEADD(SECOND, 5, @t), -- 11:58:54.9843750
DATEADD(SS, 5, @t), -- 11:58:54.9843750
DATEADD(S, 5, @t), -- 11:58:54.9843750
DATEADD(MILLISECOND, 5, @t), -- 11:58:49.9893750
DATEADD(MS, 5, @t), -- 11:58:49.9893750
DATEADD(MICROSECOND, 5, @t), -- 11:58:49.9843800
DATEADD(MCS, 5, @t), -- 11:58:49.9843800
DATEADD(NANOSECOND, 550, @t), -- 11:58:49.9843756
DATEADD(NS, 550, @t) -- 11:58:49.9843756

We will examine the other data types in the next post.

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 1 - Variable initialization and Compound Assignment Operators
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2