C# Tutorials and offshore development in India

Prizes & Awards

My Profile

AdSense Revenue

Active Members
Today

Last 7 Days

more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !

Using the TIME data type in SQL Server 2008

Posted Date: 06 May 2008 Resource Type: Articles Category: Databases

Posted By: kunal badgujar Member Level: Gold
Rating:

Points: 15

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.

Responses

Author: Sebastian 13 Jun 2008

Member Level: Gold Points : 1

This is very informative. Thanks for sharing the details.

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Writing Outer Joins in T-SQL
	Previous Resource: Integrating Profiler and PerfMon Log Files
	Return to Discussion Resource Index
	Post New Resource
	Category: Databases

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

audio conferencing services