SQL Server TIME Data Type: Range, Precision and Usage

3 min read

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.

On this page
  1. Range and precision of TIME(n)
  2. Choosing TIME vs DATETIME vs DATETIME2 vs TIMESTAMP data type
  3. Converting and formatting TIME in SQL Server
  4. SQL Server time data type examples: insert, compare and order
  5. Storage and compatibility notes
  6. FAQ
  7. Sources

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]'}.

ScalePrecision and scaleBytesExample literal
TIME (default)(16,7)512:34:56.1234567
TIME(0)(8,0)312:34:56
TIME(1)(10,1)312:34:56.1
TIME(2)(11,2)312:34:56.12
TIME(3)(12,3)412:34:56.123
TIME(4)(13,4)412:34:56.1234
TIME(5)(14,5)512:34:56.12345
TIME(6)(15,6)512:34:56.123456
TIME(7)(16,7)512: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

TypeRange / timePrecision and accuracyStorageDate and offsetC# equivalentUse case
TIME(n)00:00:00.0000000–23:59:59.9999999scale 0–7; 100 ns3–5 bytestime onlyTimeSpanshifts, schedules, durations
DATETIME1753-01-01 to 9999-12-313.33 ms8 bytesdate + timeDateTimelegacy date/time columns
DATETIME2(n)0001-01-01 to 9999-12-31scale 0–7; 100 ns6–8 bytesdate + timeDateTimemodern date/time columns
TIMESTAMP / rowversionbinary row versionnot a time type8 bytesno date or timebyte[]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:

ShiftIDStartTimeEndTime
107:00:00.0015: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.

SQL blogcasts

SET_NOCOUNT_ON

Technical notes on SQL Server for DBAs and developers: why a query or job behaves as it does and how to fix it, with T-SQL that runs and the version it was tested on.

More notes

Go up