TIME DATA-TYPE
Time data type is a one of the new data type introduced in SQL Server 2008. It provided you the time with the accuracy of 100 nanoseconds.
Reason for this data type
There are lot of scenarios where, we need to get only time. In our legacy system, we dont have an option to get only time. We need to do some login on the datetime column to get the time.
Let's play around this Time datatype in our SSMS :-)
-------------------------------------------------------------------------------------
DECLARE @VAL TIME
SELECT @VAL = GETDATE()
PRINT @VAL
The output is
18:45:29.0230000 ( The format is hour:Minute:Second:Nanoseconds upto 7 precision)
-------------------------------------------------------------------------------------
Memory space taken
This datatype will take 3 to 5 bytes memory.
Let's see another example,
-------------------------------------------------------------------------------------
DECLARE @VAL TIME(0) -- This will take 3 bytes to store. Because it doesn't have nanoseconds to store
DECLARE @VAL1 TIME(7) -- -- This will take 5 bytes to store. Because, it holds nanoseconds
SELECT @VAL = GETDATE()
SELECT @VAL1= GETDATE()
PRINT @VAL
PRINT @VAL1
The output is,
18:48:43 -- 3 bytes to store only hour:minute:seconds
18:48:42.5530000 -- 5 bytes to store
-------------------------------------------------------------------------------------
Implicit conversion from datetime datatype
From Datetime to time, implicit conversion will happen. No need to explicitly convert the datatypes. Below is an example for the same.
-------------------------------------------------------------------------------------
DECLARE @VAL DATETIME
DECLARE @VAL1 TIME(7)
SELECT @VAL = GETDATE()
SELECT @VAL1 = @VAL
PRINT @VAL
PRINT @VAL1
The Output is,
Aug 23 2010 6:51PM
18:51:40.3500000
-------------------------------------------------------------------------------------