time (Transact-SQL)

**APPLIES TO:** SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

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

[!NOTE]
Informatica information is provided for PDW customers using the Informatica Connector.

time Description

Supported String Literal Formats for time

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

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.

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.

Backward Compatibility for Down-level Clients

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following table shows the type mapping between an up-level instance of SQL Server and down-level clients.

Converting Date and Time Data

When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL)

Converting time(n) Data Type to Other Date and Time Types

This section describes what occurs when a time data type is converted to other date and time data types.

When the conversion is to time(n), the hour, minute, and seconds are copied. When the destination precision is less than the source precision, the fractional seconds is rounded up to fit the destination precision. The following example shows the results of converting a time(4) value to a time(3) value.

DECLARE @timeFrom time(4) = '12:34:54.1237';  
DECLARE @timeTo time(3) = @timeFrom;  
  
SELECT @timeTo AS 'time(3)', @timeFrom AS 'time(4)';  
  
--Results  
--time(3)      time(4)  
-------------- -------------  
--12:34:54.124 12:34:54.1237  
--  
--(1 row(s) affected)  

If the conversion is to
date, the conversion fails, and error message 206 is raised: “Operand type clash: date is incompatible with time”.

When the conversion is to datetime, hour, minute, and second values are copied; and the date component is set to ‘1900-01-01’. When the fractional seconds precision of the time(n) value is greater than three digits, the datetime result will be truncated. The following code shows the results of converting a time(4) value to a datetime value.

DECLARE @time time(4) = '12:15:04.1237';  
DECLARE @datetime datetime= @time;  
SELECT @time AS '@time', @datetime AS '@datetime';  
  
--Result  
--@time         @datetime  
--------------- -----------------------  
--12:15:04.1237 1900-01-01 12:15:04.123  
--  
--(1 row(s) affected)  
  

When the conversion is to smalldatetime, the date is set to ‘1900-01-01’, and the hour and minute values are rounded up. The seconds and fractional seconds are set to 0. The following code shows the results of converting a time(4) value to a smalldatetime value.

-- Shows rounding up of the minute value.  
DECLARE @time time(4) = '12:15:59.9999';   
DECLARE @smalldatetime smalldatetime= @time;    
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';   
  
--Result  
@time            @smalldatetime  
---------------- -----------------------  
12:15:59.9999    1900-01-01 12:16:00--  
--(1 row(s) affected)  
  
-- Shows rounding up of the hour value.  
DECLARE @time time(4) = '12:59:59.9999';   
DECLARE @smalldatetime smalldatetime= @time;    
  
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';  
@time            @smalldatetime  
---------------- -----------------------  
12:59:59.9999    1900-01-01 13:00:00  
  
(1 row(s) affected)  
  

If the conversion is to datetimeoffset(n), the date is set to ‘1900-01-01’, and the time is copied. The time zone offset is set to +00:00. When the fractional seconds precision of the time(n) value is greater than the precision of the datetimeoffset(n) value, the value is rounded up to fit. The following example shows the results of converting a time(4) value to a datetimeoffset(3) type.

DECLARE @time time(4) = '12:15:04.1237';  
DECLARE @datetimeoffset datetimeoffset(3) = @time;  
  
SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset';  
  
--Result  
--@time         @datetimeoffset  
--------------- ------------------------------  
--12:15:04.1237 1900-01-01 12:15:04.124 +00:00  
--  
--(1 row(s) affected)  
  

When converting to datetime2(n), the date is set to ‘1900-01-01’, the time component is copied, and the time zone offset is set to 00:00. When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value will be rounded up to fit. The following example shows the results of converting a time(4) value to a datetime2(2) value.

DECLARE @time time(4) = '12:15:04.1237';  
DECLARE @datetime2 datetime2(3) = @time;  
  
SELECT @datetime2 AS '@datetime2', @time AS '@time';  
  
--Result  
--@datetime2              @time  
------------------------- -------------  
--1900-01-01 12:15:04.124 12:15:04.1237  
--  
--(1 row(s) affected)  

Converting String Literals to time(n)

Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. Otherwise, a runtime error is raised. Implicit conversions or explicit conversions that do not specify a style, from date and time types to string literals will be in the default format of the current session. The following table shows the rules for converting a string literal to the time data type.

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';  

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.

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.

See Also

CAST and CONVERT (Transact-SQL)