SQL Server TIME Data Type: Range, Precision and Usage

The SQL Server TIME data type stores a time of day on a 24-hour clock, from 00:00:00.0000000 to 23:59:59.9999999. Use TIME(n) for fractional seconds precision 0–7; storage is 3–5 bytes. Choose TIME for times, durations and recurring schedules; use datetime2 or datetimeoffset when you also need a date or time zone.
Range and precision of TIME(n)
Microsoft Learn defines TIME as a time of day without time zone awareness. The fractional second scale n accepts 0–7 and defaults to 7. The hour ranges from 0 to 23, minutes and seconds range from 0 to 59, and fractional seconds can hold zero to seven digits.
The default string literal format is hh:mm:ss[.nnnnnnn]. SQL Server also accepts the ISO 8601 form hh:mm:ss and the ODBC escape form {t 'hh:mm:ss[.fractional seconds]'}.
| Scale | Precision and scale | Bytes | Example literal |
|---|---|---|---|
TIME (default) | (16,7) | 5 | 12:34:56.1234567 |
TIME(0) | (8,0) | 3 | 12:34:56 |
TIME(1) | (10,1) | 3 | 12:34:56.1 |
TIME(2) | (11,2) | 3 | 12:34:56.12 |
TIME(3) | (12,3) | 4 | 12:34:56.123 |
TIME(4) | (13,4) | 4 | 12:34:56.1234 |
TIME(5) | (14,5) | 5 | 12:34:56.12345 |
TIME(6) | (15,6) | 5 | 12:34:56.123456 |
TIME(7) | (16,7) | 5 | 12:34:56.1234567 |
The default precision is 100 nanoseconds. Scale 0–2 uses 3 bytes, 3–4 uses 4 bytes, and 5–7 uses 5 bytes.
Choosing TIME vs DATETIME vs DATETIME2 vs TIMESTAMP data type
| Type | Range / time | Precision and accuracy | Storage | Date and offset | C# equivalent | Use case |
|---|---|---|---|---|---|---|
TIME(n) | 00:00:00.0000000–23:59:59.9999999 | scale 0–7; 100 ns | 3–5 bytes | time only | TimeSpan | shifts, schedules, durations |
DATETIME | 1753-01-01 to 9999-12-31 | 3.33 ms | 8 bytes | date + time | DateTime | legacy date/time columns |
DATETIME2(n) | 0001-01-01 to 9999-12-31 | scale 0–7; 100 ns | 6–8 bytes | date + time | DateTime | modern date/time columns |
TIMESTAMP / rowversion | binary row version | not a time type | 8 bytes | no date or time | byte[] | optimistic concurrency |
DATETIME2 is the modern extension of DATETIME: it has a larger date range, larger default fractional precision, and optional user-specified precision. TIMESTAMP does not store a clock time. It is a database-generated binary number that increments when a row changes, and rowversion is the preferred synonym.
Converting and formatting TIME in SQL Server
Time format in SQL Server usually means style 108 for CONVERT or a custom FORMAT string:
SELECT
CAST('12:34:56.1234567' AS TIME(7)) AS CastValue,
CONVERT(varchar, CAST('12:34:56' AS TIME), 108) AS Style108,
FORMAT(CAST('12:34:56' AS TIME), 'hh\:mm\:ss') AS FormattedValue;Style 108 returns HH:mm:ss in 24-hour format. Style 114 returns HH:mm:ss:fff with milliseconds. FORMAT supports .NET format strings, so 'hh\:mm\:ss' is easier to read than 'hh:mm:ss' in T-SQL.
SQL Server time data type examples: insert, compare and order
The example creates a shifts table, inserts two rows, and selects only shifts where the start time is earlier than the end time.
CREATE TABLE Shifts (
ShiftID int IDENTITY(1,1) PRIMARY KEY,
StartTime time(2) NOT NULL,
EndTime time(2) NOT NULL
);
INSERT INTO Shifts (StartTime, EndTime)
VALUES ('07:00:00.00', '15:30:00.00'),
('22:00:00.00', '06:00:00.00');
SELECT ShiftID, StartTime, EndTime
FROM Shifts
WHERE StartTime < EndTime
ORDER BY StartTime;Result:
| ShiftID | StartTime | EndTime |
|---|---|---|
| 1 | 07:00:00.00 | 15:30:00.00 |
The second row is omitted because 22:00:00.00 < 06:00:00.00 is false within the same 24-hour day. SQL Server compares and orders TIME values on a 24-hour clock without crossing midnight automatically. For shifts that cross midnight, store the date in a separate DATE or datetime2 column.
Storage and compatibility notes
TIME(n) storage is fixed per scale, not variable. The values shown are for uncompressed rowstore; compression or columnstore may change disk and memory size. Keep the database in compatibility level 100 or later when you define TIME columns. On SQL Server 2019 CU18, the examples above run unchanged at the default compatibility level. How page splits work explains rowstore page layout for fixed-size columns like TIME.
Down-level clients receive TIME as HH:mm:ss[.nnnnnnn] through ODBC, OLEDB, JDBC, or SQLCLIENT. The default value is 00:00:00, used for the appended time part when SQL Server implicitly converts date to datetime2 or datetimeoffset.
If a long-running conversion or comparison on a TIME column blocks another session, Event Notifications for blocked processes shows how to capture the incident.
FAQ
What is the range of SQL Server TIME data type? From 00:00:00.0000000 to 23:59:59.9999999; no date or time zone offset.
What does TIME(n) mean? n is the fractional second precision from 0 to 7; default is 7.
How many bytes does SQL Server TIME use? 3 bytes for scale 0–2, 4 for 3–4 and 5 for 5–7.
What is the difference between TIME and DATETIME2? TIME stores only time of day; datetime2 stores date plus time.
Does SQL Server TIMESTAMP store time? No; timestamp/rowversion is a binary row-version value, not a date or time type.
Sources
Microsoft Learn, time (Transact-SQL) Microsoft Learn, CAST and CONVERT (Transact-SQL) Microsoft Learn, datetime2 (Transact-SQL)
Reviewed by sqlblogcasts.com Editors.

More notes