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!
Best Answer
-
If it is coming in as a STRING, you could either change it in the Dataflow, or in the Beast mode.
You could use the STR_TO_DATE function. Such as,
STR_TO_DATE(str,format);
Example: STR_TO_DATE(`date`, '%d %m %Y)Please let me know if this helped.
3
Answers
-
Hi @ocrkm,
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,
Matt Chandler
Domo0 -
@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 -
If it is coming in as a STRING, you could either change it in the Dataflow, or in the Beast mode.
You could use the STR_TO_DATE function. Such as,
STR_TO_DATE(str,format);
Example: STR_TO_DATE(`date`, '%d %m %Y)Please let me know if this helped.
3 -
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:
STR_TO_DATE('1/31/2015', '%m/%d/%Y')
4 -
Glad that worked for you!
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive