Sql Server 2008 introduced new data type with name TIME. This new data type just stores the time. The n in TIME (n) defines this level of fractional second precision, from 0 to 7 digits of precision. It stores time with a range of 00:00:00.0000000 through 23:59:59.9999999.
Storage difference:
TIME data type takes only 3-5 bytes for storage depend upon precision you requested. It saves many bytes like if you want to store TIME with less precision.
Internal functions support:
As we know that there are some functions uses for DATETIME data type formatting like DATEPART, DATENAME, DATEDIFF, and DATEADD, these function are now supported with new TIME type also.
Declaration:
Declare with different precision levels
0 Precision Level
DECLARE @time_now_0_Prec time(0)
1 Precision Level
DECLARE @time_now_1_Prec time(1)
2 Precision Level
DECLARE @time_now_2_Prec time(2)
3 Precision Level
DECLARE @time_now_3_Prec time(3)
4 Precision Level
DECLARE @time_now_4_Prec time(4)
5 Precision Level
DECLARE @time_now_5_Prec time(5)
6 Precision Level
DECLARE @time_now_6_Prec time(6)
7 Precision Level
DECLARE @time_now_7_Prec time(7)
Value Assignment:
SET @time_now_7_Prec = SYSDATETIME()
Use In T-Sql:
SELECT @time_now_0 as 'Current Time', datalength(@time_now_0) as 'Size in bytes'
Result:
Current Time Size in Bytes
------------------ -----------------
20:01:44.8035344 5
