Specify different date formats for multiple sources in ETL

I am combining a number of data sources with date columns using an ETL before performing other calculations on them.

 

Some of the sources have Australian date format (DD/MM/YYYY), and others have US format (MM/DD/YYYY).

 

The ETL gives me results in YYYY-MM-DD format, where it's actually incorrectly converted some of the dates to YYYY-DD-MM by assuming the wrong input date format. This means I end up with results from the future (eg 2018-12-03).

 

What's the best way to ensure the ETL always knows the correct input date format?

Tagged:

Comments

  • Hi. @artywah

     

    Is it possible to avoid by replacing the date that is incorrectly converted by using regex?

     

    Set Column Type
    Column : DateColumn
    Data Type: Text

     Pattern A YYYY-MM-DD (TEXT)

    Set Column Type
    Column : DateColumn
    Data Type: Date
    Format : Year first


     Pattern B YYYY-DD-MM (TEXT)

      Replace Text
       Column : DateColumn
    Search "Use Regex": ^([0-9]*)-([0-9]*)-([0-9]*)
    Replace : $1-$3-$2

    Set Column Type
    Column : DateColumn
    Data Type: Date
    Format : Year first
  • Unfortnately this isn't working. It seems Domo is being too smart and processing the format before I can do anything with it in an ETL.

     

    It seems I need to set the date format as Domo imports the data (before I can perform an ETL on it)

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!