time (Transact-SQL)

time (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2008 Books Online (October 2009)

time (Transact-SQL)

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

time Description

Property	Value
Syntax	time [ (fractional second precision) ]
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds precision	Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. The default fractional precision is 7 (100ns).
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
Default string literal format (used for down-level client)	hh:mm:ss[.nnnnnnn] For more information, see the "Backward Compatibility for Down-level Clients" section of Using Date and Time Data.
Range	00:00:00.0000000 through 23:59:59.9999999
Element ranges	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
Character length	8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn)
Precision, scale (user specifies scale only)	Specified scale Result (precision, scale) Column length (bytes) Fractional seconds precision time (16,7) 5 7 time(0) (8,0) 3 0-2 time(1) (10,1) 3 0-2 time(2) (11,2) 3 0-2 time(3) (12,3) 4 3-4 time(4) (13,4) 4 3-4 time(5) (14,5) 5 5-7 time(6) (15,6) 5 5-7 time(7) (16,7) 5 5-7
Storage size	5 bytes, fixed, is the default with the default of 100ns fractional second precision.
Accuracy	100 nanoseconds
Default value	00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported String Literal Formats for time

The following table shows the valid string literal formats for the time data type.

SQL Server	Description
hh:mm[:ss][:fractional seconds][AM][PM] hh:mm[:ss][.fractional seconds][AM][PM] hhAM[PM] hh AM[PM]	The hour value of 0 represents the hour after midnight (AM), regardless of whether AM is specified. PM cannot be specified when the hour equals 0. Hour values from 01 through 11 represent the hours before noon if neither AM nor PM is specified. The values represent the hours before noon when AM is specified. The values represent hours after noon if PM is specified. The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, the value represents the hour that starts at midnight. If PM is specified, the value represents the hour that starts at noon. For example, 12:01 is 1 minute after noon, as is 12:01 PM; and 12:01 AM is one minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM. Hour values from 13 through 23 represent hours after noon if AM or PM is not specified. The values also represent the hours after noon when PM is specified. AM cannot be specified when the hour value is from 13 through 23. An hour value of 24 is not valid. To represent midnight, use 12:00 AM or 00:00. 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 three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.

SQL Server

Description

hh:mm[:ss][:fractional seconds][AM][PM]

hh:mm[:ss][.fractional seconds][AM][PM]

hhAM[PM]

hh AM[PM]

The hour value of 0 represents the hour after midnight (AM), regardless of whether AM is specified. PM cannot be specified when the hour equals 0.

Hour values from 01 through 11 represent the hours before noon if neither AM nor PM is specified. The values represent the hours before noon when AM is specified. The values represent hours after noon if PM is specified.

The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, the value represents the hour that starts at midnight. If PM is specified, the value represents the hour that starts at noon. For example, 12:01 is 1 minute after noon, as is 12:01 PM; and 12:01 AM is one minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM.

Hour values from 13 through 23 represent hours after noon if AM or PM is not specified. The values also represent the hours after noon when PM is specified. AM cannot be specified when the hour value is from 13 through 23.

An hour value of 24 is not valid. To represent midnight, use 12:00 AM or 00:00.

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 three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.

ISO 8601	Notes
hh:mm:ss hh:mm[:ss][.fractional seconds]	hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset. mm is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset.

ISO 8601

Notes

hh:mm:ss

hh:mm[:ss][.fractional seconds]

hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset.
mm is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset.

ODBC	Notes
{t 'hh:mm:ss[.fractional seconds]'}	ODBC API specific. Functions in SQL Server 2008 as in SQL Server 2005.

ODBC

Notes

{t 'hh:mm:ss[.fractional seconds]'}

ODBC API specific.

Functions in SQL Server 2008 as in SQL Server 2005.

time Compliance with ANSI and ISO 8601 Standards

Using hour 24 to represent midnight and leap second over 59 as defined by ISO 8601 (5.3.2 and 5.3) are not supported to be backward compatible and consistent with the existing date and time types. They are not defined by SQL standard 2003.

The default string literal format (used for down-level client) will align with the SQL standard form, which is defined as hh:mm:ss[.nnnnnnn]. This format resembles the ISO 8601 definition for TIME excluding fractional seconds.

Examples

A. Comparing date and time Data Types

The following example compares the results of casting a string to each date and time data type.

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Data type	Output
time	12:35:29. 1234567
date	2007-05-08
smalldatetime	2007-05-08 12:35:00
datetime	2007-05-08 12:35:29.123
datetime2	2007-05-08 12:35:29. 1234567
datetimeoffset	2007-05-08 12:35:29.1234567 +12:15

B. Inserting Valid Time String Literals into a time(7) Column

The following table lists different string literals that can be inserted into a column of data type time(7) with the values that are then stored in that column.

String literal format type	Inserted string literal	time(7) value that is stored	Description
SQL Server	'01:01:01:123AM'	01:01:01.1230000	When a colon (:) comes before fractional seconds precision, scale cannot exceed three positions or an error will be raised.
SQL Server	'01:01:01.1234567 AM'	01:01:01.1234567	When AM or PM is specified, the time is stored in 24-hour format without the literal AM or PM
SQL Server	'01:01:01.1234567 PM'	13:01:01.1234567	When AM or PM is specified, the time is stored in 24-hour format without the literal AM or PM
SQL Server	'01:01:01.1234567PM'	13:01:01.1234567	A space before AM or PM is optional.
SQL Server	'01AM'	01:00:00.0000000	When only the hour is specified, all other values are 0.
SQL Server	'01 AM'	01:00:00.0000000	A space before AM or PM is optional.
SQL Server	'01:01:01'	01:01:01.0000000	When fractional seconds precision is not specified, each position that is defined by the data type is 0.
ISO 8601	'01:01:01.1234567'	01:01:01.1234567	To comply with ISO 8601, use 24-hour format, not AM or PM.
ISO 8601	'01:01:01.1234567 +01:01'	01:01:01.1234567	The optional time zone difference (TZD) is allowed in the input but is not stored.

C. Inserting Time String Literal into Columns of Each date and time Date Type

In the following table the first column shows a time string literal to be inserted into a database table column of the date or time data type shown in the second column. The third column shows the value that will be stored in the database table column.

Inserted string literal	Column data type	Value that is stored in column	Description
'12:12:12.1234567'	time(7)	12:12:12.1234567	If the fractional seconds precision exceeds the value specified for the column, the string will be truncated without error.
'2007-05-07'	date	NULL	Any time value will cause the INSERT statement to fail.
'12:12:12'	smalldatetime	1900-01-01 12:12:00	Any fractional seconds precision value will cause the INSERT statement to fail.
'12:12:12.123'	datetime	1900-01-01 12:12:12.123	Any second precision longer than three positions will cause the INSERT statement to fail.
'12:12:12.1234567'	datetime2(7)	1900-01-01 12:12:12.1234567	If the fractional seconds precision exceeds the value specified for the column, the string will be truncated without error.
'12:12:12.1234567'	datetimeoffset(7)	1900-01-01 12:12:12.1234567 +00:00	If the fractional seconds precision exceeds the value specified for the column, the string will be truncated without error.

A. Comparing date and time Data Types

B. Inserting Valid Time String Literals into a time(7) Column

C. Inserting Time String Literal into Columns of Each date and time Date Type

Reference

Other Resources

Help and Information