SQL Server 2008 TIME data type
The new TIME type stores a time with a specified scale that defines the fractional second precision.
The scale ranges from 0-7 representing 0-7 significant digits for the fractional seconds. The default precision is TIME(7), giving 7 significant digits, a range of .0000000 to .9999999.
TIME is stored as an integer of various sizes, depending on the scale. For a scale of 0-2 it is stored as a 3 byte integer, 3-4 a 4 byte integer, and for scale 5-7 it is stored as a 5 byte integer.
The scale is then used to calculate the time since midnight, with an accuracy ranging from 1 second to 100 nanoseconds.
If t is the value stored in the time column and n is the scale the time from midnight in seconds can be calculated by t / 10n.
Here’s a summary of the storage and scaling (seconds, milliseconds, and nanoseconds are the respective duration t is multiplied by):
| Scale |
Storage (bytes) |
Seconds |
Milliseconds |
Nanoseconds |
| TIME(0) |
3 |
1 |
1000 |
1000000000 |
| TIME(1) |
3 |
0.1 |
100 |
100000000 |
| TIME(2) |
3 |
0.01 |
10 |
10000000 |
| TIME(3) |
4 |
0.001 |
1 |
1000000 |
| TIME(4) |
4 |
0.0001 |
0.1 |
100000 |
| TIME(5) |
5 |
0.00001 |
0.01 |
10000 |
| TIME(6) |
5 |
0.000001 |
0.001 |
1000 |
| TIME(7) |
5 |
0.0000001 |
0.0001 |
100 |
It’s possible to extract the unscaled value from a TIME value, although it requires a few steps.
DECLARE @Time TIME(7) = '00:01:00' -- Format HH:mm:SS[.nnnnnnn]
DECLARE @BinaryTime VARBINARY(8)
SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),
1,
DATALENGTH(@Time))
SELECT CONVERT(BIGINT, @BinaryTime) -- Unscaled TIME value
-- Result: 600000000
The above example gives a result of 600000000, which, looking at the scale makes sense. The scale is 7, so a time of 1 minute past midnight is 60 seconds = 600000000 / 107.
DECLARE @Time TIME(3) = '00:01:00' -- Format HH:mm:SS[.nnnnnnn]
DECLARE @BinaryTime VARBINARY(8)
SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),
1,
DATALENGTH(@Time))
SELECT CONVERT(BIGINT, @BinaryTime) -- Unscaled TIME value
-- Result: 60000
A scale of 3 gives a result of 6000 as 60 seconds = 6000 / 103
Books Online has more information about the new DATE type here.