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 110000000 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

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

Data typeprecision & scaleStorage Size
(bytes)
Example
time
*Default
*Same as time(7)
16,7509:31:35.6170000
time(0)8,0309:31:36
time(1)10,1309:31:35.6
time(2)11,2309:31:35.62
time(3)12.3409:31:35.617
time(4)13,4409:31:35.6170
time(5)14,5509:31:35.61700
time(6)15,6509:31:35.617000
time(7)16.7509:31:35.6170000

Default Format

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

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.

Time data type in SQL Server

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.

Time Formats

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

Format stringExamples
hh:mm[:ss][.fractional seconds][AM][PM]'09:30:30'
'09:31:57.999'
'09:31:57.9002640'
hh:mm[:ss][:fractional seconds][AM][PM]'15:10:20:60'
'09:31:57:999'
hhAM[PM]'10:10AM'
'10:10PM
'15PM'

Query Examples

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

Leave a Comment

Your email address will not be published.

Scroll to Top