Converting Text to Date shows Null

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.

@darien

Ah! Interesting!
If you convert a Date column into Text then the Convert option is not part of the right-click menu. :slight_smile:
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.

  1. 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.
  2. 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.
  3. 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.

Workaround

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.