Handling month or day names in different languages in Snowflake
by Mauricio Rojas, on Sep 29, 2022 2:10:21 PM
Recently I received a request inspired by this StackOverflow question:
To convert a date-string that contains the name of the month in a data-field I could use
select to_date('01October2022', 'DDMMMMYYYY')
..I didn't find a way to convert the string if the month name is not english e.g. in german like 01Oktober2022
This is interesting especially if you are dealing with international users.
So I will first address a way to handle translating names or months to different languages in Snowflake and then we will answer the original question.
I usually just use the short tags like 'pt' for Portuguese or 'de' for German, but you have many options.
Based on that we can then create these UDFs:
Those UDFs always assume indexes start with 0. So Sunday will be 0 and Monday 1 and so on. And For months January will be 0, February 1 and so on.
When you call them the idx parameter is just the index as described before, the locale will be a locale code (in theory you can use any code from locale table).
I provided two sets of udfs for short and long names.
Ok, I think that is good. So now we have a way to get weekdays and month names in different languages.
But the question was different. In that question, we had a string with a day in a foreign language and we needed to use a conversion from text to date.
Ok so we can do that. Here comes another UDFs to the rescue:
This UDF will replace your foreign language month names and change them to English and then we can use our date functions.
So to answer the previous question, this will be a way to run it:
And this is how it will look when this is run in Snowflake