Convert String (yyyy-mm-dd) to Date field

[tquinn-16436437]

I have a field in my database that is a string field but it contains dates in the following format: “yyyy-mm-dd” - I need to convert this string into a date dimension so that I can use it in calculations. All of my attempts are not working. My field is called subscription_valid_from. I have included the errors I received in big query. 

Attempt 1: 

parse_date("%Y%m%d", subscription_valid_from)

error: Failed to parse input string "2021-10-08"

Attempt 2: 

cast(subscription_valid_from as date)

error: Invalid date: '05/06/2021'

Can anyone please help?

[Dawid]

If it’s yyyy-mm-dd then you would have a problem with CAST function but the error just showed us that you have multiple different formats in one column. Can you perform some checks on the column to find out how many different formats you have?