Convert text field to date format
I need to convert a text field to a date field. I have tried magic ETL which didn't work. I also created a redshift using a case statement "cast(week_end as DATE) as week_end" , which resulted in a sytax error "invalid operation: error converting text to date". any ideas??
Thanks!
Best Answers
-
Hey @debbie_a,
In beast mode you can use the STR_TO_DATE() function to convert your string to date.
However it sounds like you might have some bad data in your column if the CAST function is erroring out, you may want to check the distinct values of the date column for any odd characters or strange values.
See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date for instructions on how to use the STR_TO_DATE function.
Hope this is helpful!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'2 -
if you are using redshift, you can use TO_DATE(`string`, format)
example:
select to_date ('02 Oct 2001', 'DD Mon YYYY');
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
After I found the bad data in a row, the to_date worked as the final step to convert the data type. thanks!
1
Answers
-
Hey @debbie_a,
In beast mode you can use the STR_TO_DATE() function to convert your string to date.
However it sounds like you might have some bad data in your column if the CAST function is erroring out, you may want to check the distinct values of the date column for any odd characters or strange values.
See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date for instructions on how to use the STR_TO_DATE function.
Hope this is helpful!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'2 -
if you are using redshift, you can use TO_DATE(`string`, format)
example:
select to_date ('02 Oct 2001', 'DD Mon YYYY');
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
I did find some bad data, so you were correct on that call out!! thank you
0 -
Thanks. After finding the bad data that in a row, the to_date worked as the final step to solve the conversion issue!
0 -
After I found the bad data in a row, the to_date worked as the final step to convert the data type. thanks!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive