Monday 14 May 2012

TIME data type


SQL Server 2008 introduced a new data type called TIME, which allow the TIME without DATE. Before SQL Server 2008 it is not possible.
In this article I am going to explain the TIME data type by creating scenario to understand it better way.

Scenario-1 [ The TIME data type ]

The Example
DECLARE @tm TIME = '16:30:12'
SELECT  @tm As [Time]
Result
Time
16:30:12.0000000

Scenario-2 [ The TIME data type Accuracy ]

The default accuracy of TIME data type is 100 nanoseconds. It also allows us to define the accuracy. This indicates how many places to the right of the decimal are stored for the second's portion. We can use 0 to 7 places to the right of the decimal.
Example
DECLARE @tm0 TIME(0) = '16:32:19.1234567',
        @tm7 TIME(7) = '16:32:19.1234567'
SELECT  @tm0 AS [Time0], @tm7 AS [Time7]
Result Set
Time0             Time7
16:32:19          16:32:19.1234567

Scenario-3 [ The TIME data type Storage ]
A TIME(0) takes three bytes to store and a TIME(7) takes five bytes to store. 

Scenario-4 [ The TIME data type Conversion ]
TIME will do the implicit conversion from DATETIME and stores only the time portion of it.
Example
DECLARE     @dt1 DATETIME = '12/29/2007 12:43:24.42',
            @tm1 TIME(2)
SELECT      @tm1 = @dt1
SELECT      @tm1 As [TimeOnly]
Result Set
TimeOnly
12:43:24.42

Scenario-5 [ The TIME data with TIME ZONE ]

The TIME() does not include any time zone information, it will accept a time with time zone information but will ignore the time zone info when displayed.
Example
DECLARE @tm TIME(0) = '12:45:11 -05:30'
SELECT  @tm AS [Time]
Result
Time
12:45:11


Hope you like it.

Posted by: MR. JOYDEEP DAS


No comments:

Post a Comment