I have to format several different time formats from several CSVs. Now I have a Problem with following timestamp:

31 Mai 2022 22:41:19 UTC

I tried with

   SELECT to_timestamp('31 mai 2022 23:22:01 UTC', 'DD MON YYYY HH24:MI:SS UTC')

This is not working out because the name of mai is not recognized as a month, with following error message

Can't parse '31 mai 2022 23:22:01 UTC' as timestamp with format 'DD MON YYYY HH24:MI:SS UTC'

So when I change mai to may it's working fine

   SELECT to_timestamp('31 may 2022 23:22:01 UTC', 'DD MON YYYY HH24:MI:SS UTC')

So my suggestion would be to use REPLACE function to set it to the english expression. But it's not very elegant and I would need to implement it for the months of March, Oct and Dec as well.

Is there maybe a different pattern I can use, so Snowflake recognizes that it is a german expression ('DD MON YYYY HH24:MI:SS UTC')?

I already tried to change my SESSION timezone to 'Europe/Berlin' but this was not working out.

CodePudding user response：

The input and output format for the Timestamp, date, etc needs to be fixed. custom input is not allowed.

Please refer to these links for more details. It has examples as well.

1. https://docs.snowflake.com/en/user-guide/date-time-input-output.html#session-parameters-for-dates-times-and-timestamps

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

3. https://docs.snowflake.com/en/sql-reference/functions/to_timestamp.html

CodePudding user response：

Even the German Snowflake documentation reflects English month names so it appear you will need to follow BdR's suggestion to remap the values.