Convert type

Matillion ETL
1

hello,

my data source has a date field in the format d / mm / yyyy example 18/3/2021

I extract the data in STRING type.

is it possible to convert it to type DATE? and how to do it please?

My database is Google BigQuery, when I use Convert Type Component I have this error "Invalid 03/18/2021 as DATE"

Thanks

 

2

Hi @tatus_parker​ ,

I can't speak for BigQuery since we are a Snowflake customer but we have done this in the past using Snowflake's SQL functions to convert the string to a date. I would think that BigQuery has functions to do this type of conversion. I hope this helps. Post back if you need more direction and maybe someone on the Google cloud can chime in. Thanks for posting!

3

Hi @Bryan​

Please, Can you tell me which function you are using? Sample code

Thanks

4

Hi @tatus_parker​,

Like I said before, we are not a BigQuery customer but this is what we would do to convert a date as string to an actual date that uses a specific format: SELECT TO_DATE('18/3/2021', 'DD/MM/YYYY') as d;

Just a quick Google search led me to the equivalent function which is PARSE_DATE. Here is the doc for it: https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date

This is how we would handle doing string to date conversions. I hope this gets you further.

5

Hi @Bryan​

Thanks