- Categories:
Semi-structured and structured data functions (Type Predicates)
IS_TIME¶
Verifies whether aVARIANT argument contains aTIME value.
Syntax¶
IS_TIME(<variant_expr>)
Arguments¶
variant_exprAn expression that evaluates to a value of type VARIANT.
Returns¶
Returns a BOOLEAN value or NULL.
Returns TRUE if the VARIANT value contains a TIME value. Otherwise, returns FALSE.
If the input is NULL, returns NULL without reporting an error.
Examples¶
Return all of the TIME values in a VARIANT column.
Note
The output format for TIME values is set using theTIME_OUTPUT_FORMAT parameter. The default setting isHH24:MI:SS.
Create and load a table with various date and TIME values in a VARIANT column:
CREATEORREPLACETABLEvardttm(vVARIANT);
INSERTINTOvardttmSELECTTO_VARIANT(TO_DATE('2024-02-24'));INSERTINTOvardttmSELECTTO_VARIANT(TO_TIME('20:57:01.123456789+07:00'));INSERTINTOvardttmSELECTTO_VARIANT(TO_TIMESTAMP('2023-02-24 12:00:00.456'));INSERTINTOvardttmSELECTTO_VARIANT(TO_TIMESTAMP_LTZ('2022-02-24 13:00:00.123 +01:00'));INSERTINTOvardttmSELECTTO_VARIANT(TO_TIMESTAMP_NTZ('2021-02-24 14:00:00.123 +01:00'));INSERTINTOvardttmSELECTTO_VARIANT(TO_TIMESTAMP_TZ('2020-02-24 15:00:00.123 +01:00'));
Use theTYPEOF function in a query to show the data types of the values stored in the VARIANT columnv:
SELECTv,TYPEOF(v)AStypeFROMvardttm;
+---------------------------------+---------------+| V | TYPE ||---------------------------------+---------------|| "2024-02-24" | DATE || "20:57:01" | TIME || "2023-02-24 12:00:00.456" | TIMESTAMP_NTZ || "2022-02-24 04:00:00.123 -0800" | TIMESTAMP_LTZ || "2021-02-24 14:00:00.123" | TIMESTAMP_NTZ || "2020-02-24 15:00:00.123 +0100" | TIMESTAMP_TZ |+---------------------------------+---------------+
Show the TIME values in the data by using the IS_TIME function in a WHERE clause. Only the TIME valueis returned in the output. The DATE and TIMESTAMP values aren’t returned.
SELECTvFROMvardttmWHEREIS_TIME(v);
+------------+| V ||------------|| "20:57:01" |+------------+