# Need to calculate the difference between two dates with proper date format

Options
Member
edited July 2023

Need to calculate the difference between two dates in Days Hours Mins seconds format.

Wrote the below case for date difference but not getting the required output.

Getting output as shown above in yellow:

Need output as : 4days 21 Hrs 20Min 27sec

Tagged:

• Coach
Options

You can use the SPLIT_PART function and CONCAT function to build this. It would look something like this:

```CONCAT(
split_part(`Time`,' ',1),' days ',
RIGHT(split_part(`Time`,':',1),2),' hours ',
split_part(`Time`,':',2),' minutes ',
split_part(split_part(`Time`,':',3),'.',1),' seconds '
)
```

You would need to add additional case logic if you didn't want the days to show if there were no days.

Hope this helps.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Member
Options

Hi Mark, thanks this worked for one of the rows. But for other row it is not working where the day is 0. can you please help.

• Coach
Options

It looks like your data has extra spaces at the end, which is why it is displaying the full value at the beginning when there is no day at the start. Adding a TRIM function around the field would help. Also, looking to see if there is a space towards the start of the string to see if there is a day value will work as well. You could do this:

```CASE WHEN LEFT(INSTR(`Time`,' '),6) > 1 THEN
CONCAT(
split_part(`Time`,' ',1),' days ',
RIGHT(split_part(`Time`,':',1),2),' hours ',
split_part(`Time`,':',2),' minutes ',
split_part(split_part(`Time`,':',3),'.',1),' seconds '
)
ELSE
CONCAT(
RIGHT(split_part(`Time`,':',1),2),' hours ',
split_part(`Time`,':',2),' minutes ',
split_part(split_part(`Time`,':',3),'.',1),' seconds '
)
END
```

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Coach
edited July 2023
Options

@MarkSnodgrass awesome approach, definitely cleaner than what I was thinking about. Tested and works perfectly except for when the outcome does not includes a day part. Adding two case statements to it can help solve for those instances.

```CONCAT(
CASE WHEN `dt`  < CURRENT_TIMESTAMP() THEN '-' ELSE '' END,
CASE WHEN split_part(`dt` - CURRENT_TIMESTAMP(),' ',1) * 1 = split_part(`dt` - CURRENT_TIMESTAMP(),' ',1) THEN CONCAT(ABS(split_part(dt - CURRENT_TIMESTAMP(),' ',1)), ' days ') ELSE '' END,
RIGHT(split_part(`dt` - CURRENT_TIMESTAMP(),':',1),2),' hours ',
split_part(`dt`  - CURRENT_TIMESTAMP(),':',2),' minutes ',
split_part(split_part(`dt` - CURRENT_TIMESTAMP(),':',3),'.',1),' seconds '
)
```

• Member
Options

HI Guys, not receiving the desired results. :(

• Coach
Options

When troubleshooting these kinds of things, I will temporarily create multiple beast modes that is just extracting a single component so I can see where the logic is off. Try making a beast mode just for days using the logic for days and see if that reveals to you what additional logic you might need to add.

My days logic is looking for what is just before the first space and your result doesn't seem to be corresponding to that. Breaking it down into smaller pieces can help reveal the issue.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Coach
Options

This post I've written in the past may be beneficial for your reference on formatting different date differences with the number of days: