Beast Mode, Number to Date

user05455

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!

 

RGranada

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.

RGranada

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.

user05455

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!

 

 

user03784

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 UsedMissing Date Calculation/Aggregation Field