SQL Server TIME Data Type

The TIME data type in SQL Server is used to store time values, representing the time of day (hours, minutes, seconds, and milliseconds) without the date part.

Syntax

The syntax of the TIME data type is as follows:

TIME [ (fractional_seconds_scale) ]

fractional_seconds_scale is optional and represents the number of decimal places for seconds. If not specified, it defaults to 0.

Usage

The TIME data type is primarily used for storing time values, such as activity durations, estimated arrival times, and can also be used to store timestamps in SQL Server.

Examples

Here are two examples of using the SQL Server TIME data type.

Example 1: Storing Activity Durations

Suppose there is a table Activity with a name and duration (using the TIME data type) column. Here are some sample data:

You can create the Activity table using the following SQL statement:

CREATE TABLE Activity
(
  Name VARCHAR(50) NOT NULL,
  Duration TIME(6) NOT NULL
);

Example 2: Storing Timestamps

Suppose you want to store timestamps for creating and modifying records in a table. You can use two TIME data type columns, one representing the creation time, and the other representing the modification time. Here is an example table:

CREATE TABLE MyTable
(
  ID INT NOT NULL,
  CreatedTime TIME(3) NOT NULL,
  ModifiedTime TIME(3) NOT NULL
);

When inserting new rows, you can use the GETDATE() function to get the current time:

INSERT INTO MyTable (ID, CreatedTime, ModifiedTime)
VALUES (1, GETDATE(), GETDATE());

Conclusion

In SQL Server, the TIME data type is used to store time values such as activity durations or estimated arrival times, and it can also be used to store timestamps. When creating tables, you can specify the number of decimal places for seconds. Although it cannot store date information, it can be used in conjunction with other date data types (such as DATETIME).