Converting String to Date in Dataflow

ocrkm
ocrkm Member
in Connectors

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

  • Ragin_Cajun
    Ragin_Cajun Domo Employee
    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.

Answers

  • mattchandler
    mattchandler Domo Employee

    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
    Domo
  • kshah008
    kshah008 Contributor

    @ocrkm, did mattchandler's reply help answer your question?

  • ocrkm
    ocrkm Member

    @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.

  • Ragin_Cajun
    Ragin_Cajun Domo Employee
    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.

  • jaredpk
    jaredpk Domo Employee

    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"
  • ocrkm
    ocrkm Member

    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')

  • Ragin_Cajun
    Ragin_Cajun Domo Employee

    Glad that worked for you!

Categories