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]

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.
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.
DECLARE     @dt1 DATETIME = '12/29/2007 12:43:24.42',
            @tm1 TIME(2)
SELECT      @tm1 = @dt1
SELECT      @tm1 As [TimeOnly]
Result Set

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.
DECLARE @tm TIME(0) = '12:45:11 -05:30'
SELECT  @tm AS [Time]

Hope you like it.

Posted by: MR. JOYDEEP DAS

No comments:

Post a Comment