I am trying to convert a text field that holds a value “02/03/2020” to a date field. When I use the convert to Date option it returns only nulls. Whats the best way to do this?
Hey @brendon - What is the “convert to date” option you are referring to? The right-click menu does not have such option on text fields.
If you convert a Date column into Text then the Convert option is not part of the right-click menu.
Simply creating a new column with a string in the form of “06/11/2020” and converting to Date seems to work.
@brendon - It seems that there are multiple factors leading to the behavior you’re seeing.
- In query generation for BigQuery we’re using safe.timestamp() to convert the string to date. However BQ timestamp() only seems to accept 1 date format YYYY-MM-DD and so it returns null.
- BigQuery does not seem to handle well the cases when there’s a whitespace value as is the case in some of your rows. Furthermore instead of returning NULL only for that row in specific it throws an exception and aborts.
- To allow for converting any date string of any format and convert into a timestamp we would need to use something like parse_datetime() which accepts also format specification as an argument.
In Sigma you can use the following formula to workaround the above issues safely:
CallDatetime(“PARSE_DATETIME”, “%m/%d/%Y”, [your_date_column])
Please note that the second argument “%m/%d/%Y” needs to match the format of your current date string.