Using the TIME data type in SQL Server 2008

By Bill Graziano on 6 March 2008 | 3 Comments | Tags: Data Types, SQL Server 2008 Features


SQL Server 2008 introduces a TIME data type which allows us to store the time without the date.

An example of using this is:

DECLARE @t TIME = '17:32:19'
SELECT [Time] = @t

- - - - - - - - - - - - - - - - -

Time
----------------
17:32:19.0000000

This example also shows how you can assign a value in the DECLARE statement.  The default accuracy is 100 nanoseconds.  The TIME data type also allows you to define the accuracy.  This indicates how many places to the right of the decimal are stored for the seconds portion.

DECLARE @t0 TIME(0) = '17:32:19.1234567',
		@t7 TIME(7) = '17:32:19.1234567'
SELECT [Time0] = @t0, [Time7] = @t7

- - - - - - - - - - - - - - - - -

Time0    Time7
-------- ----------------
17:32:19 17:32:19.1234567

You can define from zero to seven places to the right of the decimal.  A TIME(0) takes three bytes to store and a TIME(7) takes five bytes to store.  Declaring an accuracy of three or four would take four bytes to store.  If you declare a TIME variable without the accuracy it defaults to TIME(7).

TIME will do an implicit conversion from DATETIME and retain only the time portion.  TIME will implicitly accept strings in most common time formats.

DECLARE @d1 DATETIME = '12/19/2007 13:43:23.45',
		@t1 TIME(2)
SELECT	@t1 = @d1
SELECT	TimeOnly = @t1

- - - - - - - - - - - - - - - - -

TimeOnly
-----------
13:43:23.45

TIME doesn't include any information on the time zone.  It will accept a time with time zone information but will ignore the time zone.

DECLARE @t1 TIME(0) = '13:45:12 -05:00'
SELECT	[Time] = @t1

- - - - - - - - - - - - - - - - -

Time
--------
13:45:12

The TIME datatype is fairly straightforward and very small for the information stored.  It should come in very handy.

Discuss this article: 3 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Handling SQL Server Errors (5 April 2010)

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server 2008: Table Valued Parameters (24 July 2008)

Using the DATE data type in SQL Server 2008 (6 December 2007)

Working with Time Spans and Durations in SQL Server (15 October 2007)

DATEDIFF Function Demystified (20 March 2007)

Other Recent Forum Posts

incremented updated (6 Replies)

Add mutiplt field with auto generate number? (10 Replies)

Msg 4104 (41 Replies)

MySQL root Pwd (2 Replies)

Joining data (2 Replies)

Report spam here (915 Replies)

Update Query Help (3 Replies)

Aged Inventory (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -