SQL Server TIME Data Type

SQL Server TIME stores a time of day on a 24-hour clock, without a date or time-zone offset. Its range is 00:00:00.0000000 through 23:59:59.9999999.

Syntax

The syntax of the TIME data type is as follows:

TIME [ (fractional_seconds_scale) ]

fractional_seconds_scale sets the number of fractional-second digits and can be from 0 through 7. If omitted, SQL Server defaults to 7 digits (100-nanosecond precision). For example, TIME(0) stores whole seconds and TIME(3) stores milliseconds.

Usage

Use TIME for time-of-day values such as opening hours or a daily schedule. It does not identify a timestamp because it contains no date, and it is not a general duration type for intervals that can exceed 24 hours. Store a date separately when needed, or use DATETIME2 for a date and time together. TIME also has no time-zone offset; use DATETIMEOFFSET when the offset must be preserved.

Examples

The following examples store time-of-day values and pair a time with a separate date.

Example 1: Store opening hours

Create a table for a store’s daily opening and closing times:

CREATE TABLE StoreHours
(
  StoreID INT PRIMARY KEY,
  OpensAt TIME(0) NOT NULL,
  ClosesAt TIME(0) NOT NULL
);

Because TIME(0) has no fractional seconds, insert values such as 08:30 and 17:00:

INSERT INTO StoreHours (StoreID, OpensAt, ClosesAt)
VALUES (1, '08:30', '17:00');

Example 2: Store a date and time separately

For a schedule on a particular date, use a DATE column with the TIME column:

CREATE TABLE Appointments
(
  AppointmentID INT PRIMARY KEY,
  AppointmentDate DATE NOT NULL,
  StartsAt TIME(3) NOT NULL
);

INSERT INTO Appointments (AppointmentID, AppointmentDate, StartsAt)
VALUES (1, '2024-10-15', '09:15:30.125');

Use DATETIME2 instead when keeping the date and time in one value is more appropriate. If the value needs a time-zone offset, use DATETIMEOFFSET rather than TIME.

Conclusion

In SQL Server, TIME stores a time of day without a date or time-zone offset. Its fractional-second scale ranges from 0 to 7 and defaults to 7. Pair it with DATE for a scheduled date, use DATETIME2 for a combined date and time, or use DATETIMEOFFSET when you need to preserve an offset.