How do I fix a string error with a date?

I am getting the below error and now sure how to fix. I run this data weekly, so I am assuming I have bad data somewhere.
Best Answers
-
Where is your data coming from? If you can edit the value in the source system directly, you could change it from '0/00/2024' to a proper date. If that isn't an option, you could fix the data using a formula tile:
CASE WHEN `DateField` = '0/00/2024' THEN '12/31/2024' ELSE `DateField` END
If I solved your problem, please select "yes" above
0 -
@kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.
Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:
TRY_CAST(`Best Few Date` as DATE())
1
Answers
-
Where is your data coming from? If you can edit the value in the source system directly, you could change it from '0/00/2024' to a proper date. If that isn't an option, you could fix the data using a formula tile:
CASE WHEN `DateField` = '0/00/2024' THEN '12/31/2024' ELSE `DateField` END
If I solved your problem, please select "yes" above
0 -
I have five date fields so I am not sure the one that has the issue. All the dates are noted as the below.
Is there a way to determine which field is causing the issue?
0 -
If you can change the type, the 2012-03-14 format is preferred.
In Domo, navigate to the dataset and then inspect the schema. You'll be able to see the data type for each field. If you see a field that should be a date, but shows 'abc Text' Then that is likely where your issue is.If I solved your problem, please select "yes" above
0 -
It shows Date in my data, but string when I run it through ETL. I tried using a Alter tile and change to Date, but that causes an error. I also tried changing the file itself to text and date and both issues. Maybe I need support ticket?
0 -
Huh. So as the input it is a date, but when using it in the ETL it is converting it to a text and not allowing you to convert it back to date? Seems like you have identified which date field is causing the issue, did you find the value '0/00/2024' in that column?
If I solved your problem, please select "yes" above
0 -
@kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.
Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:
TRY_CAST(`Best Few Date` as DATE())
1 -
Thank you all! I have fixed the issue, not sure how I fixed it, but it is good now. I will go back now and see what steps worked. I appreciate all the help as it did help me determine what the issue was, and how to research it. Thank you!
0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 309 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 663 Datasets
- 119 SQL DataFlows
- 2.3K Magic ETL
- 823 Beast Mode
- Visualize
- 2.6K Charting
- 86 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 87 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 4 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 117 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 143 Governance & Security
- 11 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 116 Community Announcements
- 5K Archive