SQL TIME Data Type

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.

Example

#

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
Results:  5 records
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'.

More Examples

DATE with CAST TIME VALUE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders. Display the order dates as time (i.e. without date).
SELECT OrderNumber, FirstName, LastName, 
       CAST(OrderDate AS TIME) AS [Time],
	   TotalAmount
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
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

TIME with OTHER DATE AND TIME TYPES

Problem: Display the different date and time types in SQL Server.
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
Result:  1 record
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

You may also like



Guides


vsn 3.1