How to use IS_DATE function of Snowflake-CodePudding

I have a column 'POSTING_DATE' in snowflake table as VARCHAR data type

I have used the is_date function as below to check whether the value in the column is a date or not.

IS_DATE(TO_VARIANT(POSTING_DATE))

The to_variant conversion is used inside, is due to the fact that the function is_date supports only variant data type as argument.

The output is not correct

Can somebody please help me in getting the correct syntax of is_date function in snowflake

CodePudding user response：

As POSTING_DATE is a string, you'd be better off using TRY_TO_DATE

CodePudding user response：

To validate if a VARCHAR text is a valid date you coul use TRY_TO_DATE with explicit format:

SELECT POSTING_DATE, 
       TRY_TO_DATE(POSTING_DATE, 'YYYY-MM-DD') IS NOT NULL AS Is_valid_non_null_date
FROM tab

CodePudding user response：

IS_DATE is used for semi structured data func's and takes variant as input. As you are checking on varchar column holding date , TO_DATE() and DATE() works well for your conversion.

Examples:

  select to_date(POSTING_DATE,'AUTO'), date(POSTING_DATE, 'AUTO');
      or
  select to_date(POSTING_DATE,<FORMAT>), date(POSTING_DATE, <FORMAT>);