Converting multiple date formats (string) into date format

[TristanDraaisma]

Hi all,

newby in town, hope you forgive my ignorance on the subject. 

I have a data source where the date column is presented in multiple formats like this:
30/07/22
30-06-2022
30-06-2022
5/3/2022
3/31/2022
20 Jun 2020

When i import the sheet (Google sheets) the column is identified as a string and therefore i can't use the data as date which is the desired outcome. I also do not have change access to the source document. I would like convert this data into dates in Looker itself. I have tried to use PARSE_DATE but this only supports one format (as far as i can see in the documentation) I have used this option which does update some of the records PARSE_DATE("%d-%m-%Y", enddate)

Who can help me in finding an efficient way of converting this data into date formats instead of string format?

Thanks!

[dpillalamarri]

Hi TristanDraaisma

we can change data type in lookml in dimension below is example you can refer to:

dimension: XYZ {
type: date
sql: FORMAT_DATE('%m-%d-%Y',cast( ${TABLE}.XYZ as date));;
}

[a_shah]

I believe you are talking about using these dates in Looker Studio. There is a solution to this problem but that would require you to first figure between month and days when the number is under 12 for month and day.

This can be done in two steps, first You can create a custom field and write a CASE statement to identify if the string contains '/' or ' ' or '-' and then bring them to a single format and then parse it to the required date format. Below are sample functions in Looker Studio that may help.

CASE input_expression
WHEN expression_to_match THEN result
[WHEN expression_to_match THEN result]
[...]
[ELSE result]
END

CONTAINS_TEXT(X, text)

Hope this helps

~Ashish