Time Data Type in SQL Server

Time is the SQL Server data type that we use to store Time. It stores the time of a day, without time zone and using 24 hours format. It stores only the time without the date part.

How SQL Server stores Time

Internally SQL Server stores time as an integer. It stores the number of clock ticks after midnight. Each clock tick is ¹⁄_10000000 of a second. The lowest unit of time that you can store is 0.0000001 second. If you try to assign any time less than 0.0000001 second, it will be rounded off. It displays the time in the format hh:mm:ss[.nnnnnnn].

Syntax

time([n])

Where n is the number of digits for the fractional part of the seconds. The value of n is from 0 to 7. n is optional and defaults to 7

Choice of n defines the fractional part of the seconds. It also determines the bytes that it needs to store. Refer to the following table

Default Format

The SQL Server uses the 24 Hours format to display the time.

hh:mm:ss[.nnnnnnn]

Where

• hh represents the hour from 00 to 23
• mm represents the minute from 00 to 59
• ss is the second from 00 to 59
• nnnnnnn is the fractional part of the seconds. No of the digits comes from the column definition time(n). Also, note it does not use milliseconds

Creating Time Column

The following shows how to create a Time columns in a Table.

create table testTime (

	colTime time,
	col0 time(0),
	col1 time(1),
	col2 time(2),
	col3 time(3),
	col4 time(4),
	col5 time(5),
	col6 time(6),
	col7 time(7),
)

Inserting Values

The following insert query inserts the same time to all the time columns. We use the CURRENT_TIMESTAMP to get the current datetime. The CURRENT_TIMESTAMP function returns the current database system timestamp as a datetime value, without the database time zone offset.

insert into testTime (colTime, col0, col1, col2, col3, col4, col5, col6, col7 ) 
 values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)

colTime           col0      col1        col2        col3         col4          
----------------  --------  ----------  ----------- ------------ ------------- 
09:31:35.6170000  09:31:36  09:31:35.6	09:31:35.62 09:31:35.617 09:31:35.6170 

col5            col6            col7 
--------------  --------------- ---------------- 
09:31:35.61700	09:31:35.617000	09:31:35.6170000

Time Formats

We can insert the time in the database columns in various formats

Query Examples

insert into testTime (colTime) values ('15:10:20')

insert into testTime (colTime) values ('15:10:20.150')

insert into testTime (colTime) values ('15:10:20:15')

insert into testTime (colTime) values ('10:10AM')

insert into testTime (colTime) values ('10:10PM')

insert into testTime (colTime) values ('10AM')

Important Notes

• An hour value of 24 is not valid.
• Midnight is represented by either 12:00 AM or 00:00.
• Noon is represented by either 12:00 or 12:00 PM
• 00:mm represents the hour after midnight for example 00:05, 00:05AM
• 00:05 PM is not a valid time. PM cannot be specified when the hour equals 0.
• Milliseconds can be preceded by either a colon (:) or a period (.).
  If a colon is used, the number means thousandths-of-a-second.
  If a period is used, a single-digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and so forth.

Reference

time (Transact-SQL)

Read More