• Article

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 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 scaleResult (precision, scale)Column length (bytes)Fractional seconds precision
time (16,7)57
time(0) (8,0)30-2
time(1) (10,1)30-2
time(2) (11,2)30-2
time(3) (12,3)43-4
time(4) (13,4)43-4
time(5) (14,5)55-7
time(6) (15,6)55-7
time(7) (16,7)55-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.

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.

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.