The TIME data type specifies a time of a day without time zone awareness in SQL Server.
TIME is based on a 24-hour clock with default format of hh:mm:ss.nnnnnnn.
The default value of TIME is 00:00:00. It uses 5 bytes of storage.
A table with a TIME column.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
EmployeeId INT NOT NULL,
ScheduleFrom TIME,
ScheduleTo TIME
);
GO
INSERT INTO DemoTable VALUES (1, '8:00:00', '17:00:00');
INSERT INTO DemoTable VALUES (2, '9:30 AM', '6:30 PM');
INSERT INTO DemoTable VALUES (3, '1 PM', '10 PM');
INSERT INTO DemoTable VALUES (4, '13:00', '18:00');
INSERT INTO DemoTable VALUES (5, '2:00:00', NULL);
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
| Id | EmployeeId | ScheduleFrom | ScheduleTo |
|---|---|---|---|
| 1 | 1 | 08:00:00.0000000 | 17:00:00.0000000 |
| 2 | 2 | 09:30:00.0000000 | 18:30:00.0000000 |
| 3 | 3 | 13:00:00.0000000 | 22:00:00.0000000 |
| 4 | 4 | 13:00:00.0000000 | 18:00:00.0000000 |
| 5 | 5 | 02:00:00.0000000 | NULL |
Notice how SQL Server accepts different input time formats.
These formats are converted to the internal format which is 'HH:MM:SS.nnnnnnn'.
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
SELECT OrderNumber, FirstName, LastName,
CAST(OrderDate AS TIME) AS [Time],
TotalAmount
FROM [Order] O
JOIN Customer C ON C.Id = O.CustomerId
| OrderNumber | FirstName | LastName | Time | TotalAmount |
|---|---|---|---|---|
| 542378 | Paul | Henriot | 00:00:00.0000000 | 440.00 |
| 542379 | Karin | Josephs | 00:00:00.0000000 | 1863.40 |
| 542380 | Mario | Pontes | 00:00:00.0000000 | 1813.00 |
| 542381 | Mary | Saveley | 00:00:00.0000000 | 670.80 |
| 542382 | Pascale | Cartrain | 00:00:00.0000000 | 3730.00 |
 |
||||
CREATE TABLE DemoTable
(
MyDate DATE,
MyTime TIME,
MySmallDateTime SMALLDATETIME,
MyDateTime DATETIME,
MyDateTime2 DATETIME2,
MyDateTimeOffset DATETIMEOFFSET
);
GO
INSERT INTO DemoTable VALUES ('2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
'2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
'2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55');
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
| MyDate | MyTime | MySmallDateTime | MyDateTime | MyDateTime2 | MyDateTimeOffset |
|---|---|---|---|---|---|
| 2021-10-22 | 13:54:19.0550000 | 2021-10-22 13:54:00 | 2021-10-22 13:54:19.057 | 2021-10-22 13:54:19.0550000 | 2021-10-22 13:54:19.0550000 +00:00 |
