I am working with Snowflake into getting data from various data sources where date and date-time formats are not the same even within some systems.

We might have:

2021-11-04T00:00:00.000 06:00

or

2021-11-04

or even:

04/11/2021

I tried to use the following:

SELECT ifnull(try_to_date('2021-11-04T00:00:00.000 06:00'), to_date('2021-11-04T00:00:00.000 06:00'))

But it doesn't work when the date is like 04/11/2021

I don't want to make the query bigger as it is within a huge ingest script and 1ms counts for us.

Is there way to parse any type of date and date-time into YYYY-MM-DD for a reason that's related to our warehouse ?

CodePudding user response：

You can try different formats with a COALESCE, then the first non-null parsed will output the date:

select coalesce(
  try_to_date(mydate)
  , try_to_date(mydate, 'yyyy-mm-dd')
  , try_to_date(mydate, 'mm/dd/yyyy')) as parsed_dae

Other specifiers supported:

• https://docs.snowflake.com/en/user-guide/date-time-input-output.html#about-the-format-specifiers-in-this-section