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