Categories:

Conversion Functions , Date & Time Functions

TO_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> )

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.

Examples

These examples demonstrate the TO_TIME function.

SELECT TO_TIME('13:30:00');
+---------------------+
| TO_TIME('13:30:00') |
|---------------------|
| 13:30:00            |
+---------------------+
SELECT TO_TIME('13:30:00.000');
+-------------------------+
| TO_TIME('13:30:00.000') |
|-------------------------|
| 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                          |
+-----------------------------------+