Is there a way to combine/transform a Date&Time column into Domo?

slewis1
slewis1 Member
edited March 2023 in Datasets

So I've built a data set in another program, Midas, that I created a workbench job for and pulled it onto Domo. It has columns such as "IP Start Date" and "IP Start Time" and similar for discharge times etc. An end user is wanting to see patients broke down by Admit date, military time, and specific length of stay like "1.6" days etc.

So I was thinking of doing a date difference calculation for current date/time - the combined columns of IP start date and time. The problem I am encountering is that from Midas the start date/time is in the form of "MM/DD/YYYY" and time is military like "1706". However once the data goes through the workbench it shows in Domo for it's column in the following format "YYYY-MM-DD" and date changes to "2022-11-09T11:46:00-05:00".

Is there a way to transform the DOMO day/date format to match how it comes out of Midas and then use a date diff fxn or similar to achieve the format I mention above of "Admit Date, Admit Time via Military Time, Specific length of stay" in a table card or similar.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    That's how dates data types are formatted within Domo. If you're wanting to change the format you can use a DATE_FORMAT function in a beast mode to convert it to a string by passing in the appropriate formatting string. See https://www.w3schools.com/mysql/func_mysql_date_format.asp

    As for calculating the differences between two dates I've written up a post about this here: https://dojo.domo.com/main/discussion/52682/domo-ideas-conference-beast-modes-time-difference-formatting#latest

    It'll get you close to what you're wanting to do. You'll likely want to remove the FLOOR function since you're wanting decimal places instead of whole elapsed days. You can replace it with ROUND(..., 1) to round your value to one decimal place.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • slewis1
    slewis1 Member

    Gosh I'm trying to make sense of this, and I understand the concept overall but still struggling to get it to work right within my dataset. Like where are you pulling the universal timestamp from? I know how to get current date/time to use for calculations but not familiar with that one.

  • slewis1
    slewis1 Member

    @GrantSmith ignoring my last comment as I figured out what I was doing wrong. Focusing strictly on using the round function for elapsed days it works to take it down to 1 decimal with the Round(..., 1) but it leaves the trailing 0. For example if all the days its using is (1.91, 27.76, 16.95) etc it would round it to (1.90,27.80,17.00) is there a way to format it where it drops that tenths place 0 that it creates after the round function?

Categories