I am trying to write an sql on snowflake that checks if a column data type (value) is timestamp

select value from cat
where is_date(value)

if value is timestamp, to return 1 and 0 if its not a timestamp

CodePudding user response：

You could use TRY_TO_DATE/TRY_TO_TIMESTAMP functions:

SELECT value
FROM tab
WHERE TRY_TO_DATE(value) IS NOT NULL;

If a specific date format is expected it could be provided i.e.: TRY_TO_DATE(value, 'dd-MM-yyyy')

CodePudding user response：

You can use below format and try_to_timestamp returns NULL in case if it is invalida data and also you can apply format of Timestamp.

select try_to_timestamp('2022-01-14 15:03:43','YYYY-MM-DD HH24:MI:SS'), try_to_date('Invalid');