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].


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
*Same as time(7)

Default Format

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


  • 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'
hh:mm[:ss][:fractional seconds][AM][PM]'15:10:20:60'

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.


time (Transact-SQL)

Read More

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top