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 ]
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]
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′,
SELECT @tm1 = @dt1
SELECT @tm1 As [TimeOnly]
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