Converting String to Date in Dataflow
Hi, I have a dataflow where I combine two data sources. source1 has a date field formatted MM/DD/YYYY, and source2 has a date field formatted YYYY-MM-DD - let's call them date1 and date2. source1 also has far more records than source2, so I'm trying to perform as many calculations as possible on source2 rather than source1. My end goal is to combine these two data sources, and have Domo recognize the combined date field as a date field.
If I just use source1, then Domo recognizes that date1 is a date. However, if I transform date2 into MM/DD/YYYY, then Domo processes the combined date field as a string. And I cannot call CAST(combinedDates as date) because when I call that function on a date formatted as MM/DD/YYYY it throws an "incorrect datetime value" error.
The only way I can find to do this is to convert date1 to YYYY-MM-DD, and then call CAST(dateField as date), which then converts the date back to MM/DD/YYYY and stores it as a date type in a way that Domo recognizes.
Is there an easier way to do this? It seems like it should be easy to tell Domo to treat a field formatted as MM/DD/YYYY as a date type.
tl;dr how do I make Domo treat 10/31/2015 as a date and not a string
Thanks in advance!
Have you tried using Magic ETL? You can use the "Set Column Type" action on that column, tell it that it is a date, and tell it that it is month first (as some countries use DD/MM/YYYY for dates). You can set the job to run every time you get new data in Domo then use the resultant, cleaned up DataSet to power your cards. Let me know if that works out for you or if you have any additional questions.Best,
@kshah008not exactly, there is some additional transformation and formatting that require the use of a SQL dataflow, rather than a magic ETL.
And in any case, I've found a way to get Domo to recognize "1/1/2015" as a date, but I'm wondering if there's an easier way to do it.
It seems counterintuitive to me that if Domo reads in an excel file with "1/1/2015", it gets recognized as a date, but if I transform 2015-1-1 to that same string, it is not recognized as a date. I'm guessing that there's something about how Domo stores data that I'm just not aware of.0
You can do it either in a beast mode or dataflow. However, I recommend doing it in a dataflow. As a beast mode, it will recognize the new field as a date, but it will not be in the data grain options in card builder.I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"1
Yes, that did exactly what I needed, thanks. Apparently I didn't search through the date functions documentation thoroughly enough.
For any future searchers, this is the exact line of code I used:
Glad that worked for you!1
- 7.7K All Categories
- 3 Connect
- 919 Connectors
- 244 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 38 Visualize
- 198 Beast Mode
- 2K Charting
- 8 Variables
- 19 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 27 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部