Snowflake date conversion from varchar

Question

I have a field in my table that is a varchar datatype. I need to convert it to a date with YYYYMMDD format.

I am trying the date, to_date, try_to_date, to_timestamp functions.

SELECT
    START_DATE AS START_DATE_ORIG,
    TRY_TO_DATE(START_DATE, 'YYYYMMDD') AS START_DATE
FROM MY_TABLE;

They either error with this.

SQL Error [100097] [22007]: Can't parse '12/1/2018 12:00:00 AM' as date with format 'YYYYMMDD'

Or show me this result with null values.

Answer 1

Firstly you need to convert your varchar to date format to parse properly the date value, then you can output it again to varchar, but setting the output format for date. Try this:

SELECT
    START_DATE AS START_DATE_ORIG,
    to_varchar(TO_DATE(START_DATE, 'MM/DD/YYYY HH12:MI:SS AM'), 'YYYYMMDD') AS START_DATE
FROM MY_TABLE;

I could mixed up DD and MM, make sure it is correct for your local preferences.