In SQL Server, the TIME data type is specifically designed to store time-only values, without any date component. This type allows you to store a time value that includes hours, minutes, seconds, and fractions of a second.
Syntax
The basic syntax for defining a column with the TIME data type is as follows:
column_name TIME [ (fractional_seconds_precision) ]
- fractional_seconds_precision: This optional parameter specifies the number of digits for the fractional seconds and can range from 0 to 7. For example, TIME(3) would allow you to store milliseconds (3 decimal places).
Example of Creating a Table with TIME
Here’s how you might create a table that includes a TIME column:
CREATE TABLE MeetingSchedule (
MeetingID INT PRIMARY KEY,
MeetingTime TIME(3) NOT NULL
);
In this example, MeetingTime is defined as a TIME data type with a precision of 3, meaning it can store time values up to milliseconds.
Inserting TIME Values
When inserting values into a TIME column, you can use the following formats for the time:
- 'hh:mm:ss'
- 'hh:mm:ss.mmm' (for milliseconds)
Here’s an example of inserting data into the MeetingSchedule table:
INSERT INTO MeetingSchedule (MeetingID, MeetingTime)
VALUES (1, '14:30:00'),
(2, '09:15:30.123'),
(3, '22:45:01.456');
Querying TIME Values
To retrieve time values from a table, you can run a simple SELECT query:
SELECT MeetingID, MeetingTime
FROM MeetingSchedule;
Example Usage
You might want to retrieve meetings that occur after a certain time:
SELECT *
FROM MeetingSchedule
WHERE MeetingTime > '15:00:00';
Formatting TIME Values
When you want to display the time values in different formats, you can use the FORMAT function or convert to string using different datetime styles.
Example using FORMAT:
SELECT MeetingID, FORMAT(MeetingTime, 'hh:mm tt') AS FormattedMeetingTime
FROM MeetingSchedule;
Conclusion
The SQL Server TIME data type is useful for storing time-only values without a date component. It allows for significant precision and can be easily manipulated and queried, making it a reliable choice for applications requiring time storage.