Date Format

Options
angelsinha
angelsinha Member
in Magic ETL

Hi,

 

The date in my data set is formatted as YYYY-DD-MM. As shown below:

Date Format.PNG

 

By default domo assumes the date is YYYY-MM-DD. Therefore all my data is grouped as Jan 2016. Is there a beast mode calculation to reformat the date?

 

Thanks,
Angel

Best Answer

  • AS
    AS Coach
    Answer ✓
    Options

    Can you change the format on the input side to conform to Domo's format?  That could be the quickest solution. You could try using ETL or Beast Mode extract the date parts and reconstruct them into a new date column.  Something like this:

     

     

    STR_TO_DATE(
    CONCAT(
      YEAR(`Date`) , '-' 
      ,DAY(`Date`) , '-'
      ,MONTH(`Date`)
    )
    ,'%Y-%m-%d'
    )

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • Unknown
    Unknown
    Options

    Hi all, Can anybody help @angelsinha out? Thanks!

  • AS
    AS Coach
    Answer ✓
    Options

    Can you change the format on the input side to conform to Domo's format?  That could be the quickest solution. You could try using ETL or Beast Mode extract the date parts and reconstruct them into a new date column.  Something like this:

     

     

    STR_TO_DATE(
    CONCAT(
      YEAR(`Date`) , '-' 
      ,DAY(`Date`) , '-'
      ,MONTH(`Date`)
    )
    ,'%Y-%m-%d'
    )

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • angelsinha
    angelsinha Member
    Options

    Hi Aaron,

     

    Thanks for your help. It worked!

     

    Cheers!

    Angel

  • acgarcia21
    acgarcia21 Member
    Options

    Just so people are aware. There looks to be a small syntax error here. The solution reads:

    STR_TO_DATE(
    CONCAT(
      YEAR(`Date`) , '-' 
      ,DAY(`Date`) , '-'
      ,MONTH(`Date`)
    )
    ,'%Y-%m-%d'
    )

    It should read:

    STR_TO_DATE(
    CONCAT(
      YEAR(`Date`) , '-' 
      ,DAY(`Date`) , '-'
      ,MONTH(`Date`)
    )
    ,'%Y-%d-%m'
    )
  • NFSharma
    NFSharma Member
    Options

    You can simply do DATE_FORMAT(`Date`,'%m/%d/%y'), will save you concat.

Categories