Error with String to Date
Best Answer
-
there's a FUZZY_PARSE_DATE() function in Magic ETL, I tested with the string '/3/31/24/' and it converted it to date without an error
2
Answers
-
As shown in the error, there is a value '/3/31/24/' that is not in a date format or one that can be converted to a date. Either fix the value in the source data, or use a formula tile in Magic ETL to convert that value to '03/31/24' then use the alter columns tile to convert the string column to date.
If I solved your problem, please select "yes" above
0 -
I have checked through my dataset and there is nothing that starts with a '/'
0 -
@MichelleH Unfortunately, that did not work either.
Should I split the numbers apart then put them back together?
0 -
Can you run a preview in your ETL and show the error you get there?
If I solved your problem, please select "yes" above
0 -
there's a FUZZY_PARSE_DATE() function in Magic ETL, I tested with the string '/3/31/24/' and it converted it to date without an error
2 -
In that error message, the slashes are not part of the problematic value. They are placed around the value by the error message, the way quotes usually would be. They are very confusing and I am going to replace them with quotes.
My guess is the date format you want is '%c/%e/%y', like so:STR_TO_DATE(dateField, '%c/%e/%y')
When you can't guarantee that your months and days are zero-padded (e.g. 01/01/24, never 1/1/24), you should use %c for months and %e for days. These are forgiving, and will parse the value whether it is zero-padded or not. Their padded alternatives, %m and %d, are not forgiving. They will fail if the value is not padded. This makes them useful for parsing un-delimited formats like 010124 ('%m%d%y'). If you have a delimited format and there is any doubt at all about zero-padding, use %c and %e.Randall Oveson <randall.oveson@domo.com>
1 -
Thanks everyone!!!
Fuzzy_parse_date worked perfectly!
0 -
@ggenovese never heard of FUZZY_PARSE_DATE(), super helpful function!!!
If I solved your problem, please select "yes" above
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 99 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 727 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive