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!