Categories:

Conversion Functions , Date & Time Functions

TO_TIME , TIME

Converts an input expression into a time. If input is NULL, returns NULL.

See also:

TRY_TO_TIME

Syntax

TO_TIME( <string_expr> [, <format> ] )
TO_TIME( <timestamp_expr> )
TO_TIME( '<integer>' )
TO_TIME( <variant_expr> )

TIME( <string_expr> )
TIME( <timestamp_expr> )
TIME( '<integer>' )
TIME( <variant_expr> )

Arguments

Required:

string_expr or timestamp_expr or variant_expr or integer

Expression to be converted into a time:

  • For string_expr, the result of converting the string to a time.

  • For timestamp_expr, the time portion of the input value.

  • For a string containing an integer, the value is treated as the number of seconds since midnight. If the value is greater than or equal to 86400 (the number of seconds in a day), then the calculation is performed modulus 86400; for example, 86401 is one second after midnight.

  • For variant_expr:

    • If the variant contains a string in TIME format (‘HH:MI:SS’), a string conversion is performed.

    • If the variant contains a string in INTEGER format, a string conversion is performed and the value is treated as the number of seconds since midnight (modulus 86400 if necessary).

    • If the variant contains JSON null value, the output is NULL.

For all other values, a conversion error is generated.

Optional:

format

Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

Default: current value of the TIME_INPUT_FORMAT session parameter (usually AUTO)

For more information, see Date and Time Formats in Conversion Functions.

Returns

The data type of the returned value is TIME.

Usage Notes

  • Unlike the TO_TIME function, the TIME function does not support the optional format parameter.

Examples

These examples demonstrate the TO_TIME function.

SELECT TO_TIME('13:30:00'), TIME('13:30:00');
+---------------------+------------------+
| TO_TIME('13:30:00') | TIME('13:30:00') |
|---------------------+------------------|
| 13:30:00            | 13:30:00         |
+---------------------+------------------+

SELECT TO_TIME('13:30:00.000'), TIME('13:30:00.000');
+-------------------------+----------------------+
| TO_TIME('13:30:00.000') | TIME('13:30:00.000') |
|-------------------------+----------------------|
| 13:30:00                | 13:30:00             |
+-------------------------+----------------------+

This example shows how to use the TO_TIME function to process field separators other than the default colons (this example uses the period character as the separator between hours and minutes, and between minutes and seconds):

SELECT TO_TIME('11.15.00', 'HH24.MI.SS');
+-----------------------------------+
| TO_TIME('11.15.00', 'HH24.MI.SS') |
|-----------------------------------|
| 11:15:00                          |
+-----------------------------------+