SSRS Format calculated timespan in dd:hh:mm:ss

0
votes

I have a report and one of the fields calculates a timespan in an expression e.g

=Fields!Date1.Value-Fields!Date2.Value

I would like to display in the format dd:hh:mm:ss so days,hours,minutes and seconds. If i just leave the expression it sort of works but i get milliseconds which i don't want and if there are no days then that value disappears.

I've seen lots of examples using formatting to get HH:mm:ss but not on how to do the days.

2
reporting-servicesssrs-2008-r2rdl

2 Answers

1
votes

Try:

=Floor(DateDiff("s",Fields!Date1.Value,Fields!Date2.Value) / 86400) & ":" & 
Format(DateAdd("s", Fields!Date1.Value-Fields!Date2.Value, "00:00:00"), "HH:mm:ss")

It will give you dd:HH:mm:ss format. Note Date1 must be greater than Date2.

0
votes

Based on @alejandro's answer I ended up with 

=Format(Floor(DateDiff("s",Fields!Date1.Value,Fields!Date2.Value)/86400),"00") 
& ":" & Format(DateAdd("s", (Fields!Date2.Value-Fields!Date1.Value).TotalSeconds, 
"00:00:00"), "HH:mm:ss")

just doing the straight subtraction didn't seem to work.