interviews.dotnetthread.com

↑ Grab this Headline Animator

Tuesday, August 5, 2008

How and Why to use TIME data type in SQL Server 2008

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.0000000This 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.1234567You 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.45TIME 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

Source: http://www.sqlteam.com/article/using-the-time-data-type-in-sql-server-2008

Submit this story to DotNetKicks

No comments:

Post a Comment

Post your comments/questions/feedback for this Article.

 

Latest Articles