How to parse_date a date that is not in english?

user_13381247 0
Viewed 94

I have the following format for "dates" stored as strings, see 2 examples:

vrijdag 1 mei 2020,
donderdag 4 juni 2020

Now I want to date_parse but the problem is that this doe not work on Dutch dates. The workaround I have now is to replace the first part (the dutch day names) with '' and to replace the dutch month names with the english version. Like so:

SAFE.PARSE_DATE("%d %B %Y", REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(dutchdate,
     'maandag', ''), 'dinsdag', ''), 'woensdag', ''), 'donderdag', ''), 'vrijdag', ''
    ), 'zaterdag', ''), 'zondag', ''), 'januari','january'),'februari','february'),'maart','march'
    ),'mei','may'),'juni','june'),'juli','july'),'augustus','august'),'oktober','october'))

This looks quite stupid to me so I was wondering if there is a better way to achieve this? Like a language parameter that some other SQL dialects have?

google-bigquery sql
1 Answers

Consider approach as in below example

#standardSQL
with `project.dataset.table` as (
  select 'vrijdag 1 mei 2020' dutchdate union all
  select 'donderdag 4 juni 2020' 
), months as (
  select 'januari' month_d,'january' month_e union all
  select 'februari','february' union all
  select 'maart','march' union all
  select 'mei','may' union all
  select 'juni','june' union all
  select 'juli','july' union all
  select 'augustus','august' union all
  select 'oktober','october' 
)
select dutchdate, 
  safe.parse_date("%d %B %Y",(
    select replace(dutchdate_without_day, month_d, month_e)
    from months 
    where regexp_contains(dutchdate_without_day, month_d)
  )) parsed_date
from `project.dataset.table`, 
unnest([struct(regexp_replace(dutchdate, r'^\w+ ', '') as dutchdate_without_day)])

with output

enter image description here

user_52219440
Related