Beast Mode, Number to Date

Currenlty would love some help finding out which Beast Mode (if any) can be used to convert a number into a Date field in Domo. Currenlty the number shows up as 170,913,000,000. This should translate to 2017/09/13 000:000. I really only need the 2017/09/13 though to appear.

 

Any suggestions/help would be appreciated!

 

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    So if your field is really a number it should have no commas and you can use the following beastmode to convert it to a date field with your desired format :

     

    DATE_FORMAT(STR_TO_DATE(CONCAT(170913000000,''),'%y%m%d'),'%Y/%m/%d')

     

    If it is a string then you should use this one:

     

    DATE_FORMAT(STR_TO_DATE(REPLACE('170,913,000,000',',',''),'%y%m%d'),'%Y/%m/%d')

     

    Just replace the number (in italics) in the beastmodes with your dataset field, and it should work.

    There is some great additional information on this BeastMode functions at this two links:

     

    http://knowledge.domo.com?cid=beastmodereference

    http://knowledge.domo.com?cid=beastmodedateformat

     

    Hope this helps, don't hesitate to ask if you need more clarifications.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    So if your field is really a number it should have no commas and you can use the following beastmode to convert it to a date field with your desired format :

     

    DATE_FORMAT(STR_TO_DATE(CONCAT(170913000000,''),'%y%m%d'),'%Y/%m/%d')

     

    If it is a string then you should use this one:

     

    DATE_FORMAT(STR_TO_DATE(REPLACE('170,913,000,000',',',''),'%y%m%d'),'%Y/%m/%d')

     

    Just replace the number (in italics) in the beastmodes with your dataset field, and it should work.

    There is some great additional information on this BeastMode functions at this two links:

     

    http://knowledge.domo.com?cid=beastmodereference

    http://knowledge.domo.com?cid=beastmodedateformat

     

    Hope this helps, don't hesitate to ask if you need more clarifications.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • This worked great! I had to add %h%i%p to the STR_TO_DATE to get it to work, but it shows up as a date field now.

     

    Thank you for your assistance!

     

     

  • Hello,

     

    Thanks for the information! I have used your formula to create my date field. However, I cannot use normal date calculations or trending with the field. The box that is normally in the upper right corner  where you can select "show this quarter, past quarter, etc." is not there.

     

    What I would like to do is roll these dates up to aggregate by month on the chart, with a trendline comaring the values from same month last year. Can do this with standard date fields, but can't seem to do with this beast mode calulation.

     

    Is this a limitation of Domo with Beast Mode fields ro have I done something incorrect?

     

    Thanks

    Date Formula UsedDate Formula UsedMissing Date Calculation/Aggregation FieldMissing Date Calculation/Aggregation Field