I need to convert a text column to date in ETL

Sbhatia

Screenshot 2023-10-19 at 12.22.12.png

and I am not sure how to do this, I have attached a screenshot of my text date

MichelleH

@Sbhatia If you add an Alter Columns tile to the ETL you can change the data type of your date field from text to date. You can read more about this tile in this Knowledge Base article: https://domo-support.domo.com/s/article/360044876874?language=en_US

GrantSmith

If you have garbage data which doesn't match your date format ('Day' in your example) it may not be simple enough to just convert it to a date. You may need to conditionally set your date value:

CASE WHEN REGEXP_LIKE(`date_field`, '^\d{4}-\d{2}-\d{2}$') THEN
  STR_TO_DATE(`date_field`, '%Y-%m-%d')
END

MarkSnodgrass

You can also use TRY_CAST(myfield as date) this will null the value if it can't convert it to a date, otherwise it will make it a date formatted value.

MichelleH

@Sbhatia If you add an Alter Columns tile to the ETL you can change the data type of your date field from text to date. You can read more about this tile in this Knowledge Base article: https://domo-support.domo.com/s/article/360044876874?language=en_US

GrantSmith

If you have garbage data which doesn't match your date format ('Day' in your example) it may not be simple enough to just convert it to a date. You may need to conditionally set your date value:

CASE WHEN REGEXP_LIKE(`date_field`, '^\d{4}-\d{2}-\d{2}$') THEN
  STR_TO_DATE(`date_field`, '%Y-%m-%d')
END

pauljames

@Sbhatia I run into this all the time. The magic etl tile has let me down far too often. I'd definitely give Grants recommendation a try. Sometimes you have to do this conditional step.

MarkSnodgrass

You can also use TRY_CAST(myfield as date) this will null the value if it can't convert it to a date, otherwise it will make it a date formatted value.