Help please with a time interval formatting issue.

[FerdH4]

I'm taking two variable data items - both are date/time-combinations - and subtracting one from the other to determine the difference between them.  I've successfully done this using expressions like datediff and _minutes_between.  Again, I've been successful calculating the difference in hours, minutes, or seconds.

The problem comes when I attempt to format the output.  I need the output to look like:
" 11 hours 22 minutes 33 seconds".

Seems that no matter what I try in the way of Data Format options in the routine Report-side container dialog, I get the wrong result.  I get a format similar to the one I'm looking for with separate hours, minutes, and seconds value and the appropriate text BUT the actual numeric values are unintelligible...I can't even mentally reverse-engineer how the numbers were derived!

Anyone have specific inputs on either (a) what expression to use to create more predictable results in the Data Format, or (b) what specific Data Format choices to use regardless of the expression.

I am by the way outputting a second separate control column without any added Data Format to ensure that my mathematical expression is correct. And the results in that unformatted column are consistently correct.

[MFGF]

I'm taking two variable data items - both are date/time-combinations - and subtracting one from the other to determine the difference between them.  I've successfully done this using expressions like datediff and _minutes_between.  Again, I've been successful calculating the difference in hours, minutes, or seconds.

The problem comes when I attempt to format the output.  I need the output to look like:
" 11 hours 22 minutes 33 seconds".

Seems that no matter what I try in the way of Data Format options in the routine Report-side container dialog, I get the wrong result.  I get a format similar to the one I'm looking for with separate hours, minutes, and seconds value and the appropriate text BUT the actual numeric values are unintelligible...I can't even mentally reverse-engineer how the numbers were derived!

Anyone have specific inputs on either (a) what expression to use to create more predictable results in the Data Format, or (b) what specific Data Format choices to use regardless of the expression.

I am by the way outputting a second separate control column without any added Data Format to ensure that my mathematical expression is correct. And the results in that unformatted column are consistently correct.

Hi,

Assuming the column is the expected Time Interval data type (which it should be if you are subtracting one TimeStamp field from another), you should be able to select the column in your report, and in the Properties pane, go to the Data Format property. Set the Format type to be "Time interval", then you can configure whether or not to display each component (Years/Months/Days/Hours/Minutes/Seconds/Milliseconds) using the dropdowns.

Cheers!

MF.

[FerdH4]

MFGF - I have not tried manipulating the Properties yet.  Great idea.

I'll report back on my (hopeful) success soon.

[FerdH4]

Hi,

Assuming the column is the expected Time Interval data type (which it should be if you are subtracting one TimeStamp field from another), you should be able to select the column in your report, and in the Properties pane, go to the Data Format property. Set the Format type to be "Time interval", then you can configure whether or not to display each component (Years/Months/Days/Hours/Minutes/Seconds/Milliseconds) using the dropdowns.

Cheers!

MF.

Seems that your suggested Properties navigation and my preferred navigation through the report layout toolbar user-interface both lead to the display of the same Data Format dialog box.  Hence, no difference in the settings - I see the same values I've already set even when I navigate the Properties pane.

I think I'm going to have to try a blasted long Case expression to parse up the time interval data item and wrap it in nice text of my own.  :(

[MFGF]

Seems that your suggested Properties navigation and my preferred navigation through the report layout toolbar user-interface both lead to the display of the same Data Format dialog box.  Hence, no difference in the settings - I see the same values I've already set even when I navigate the Properties pane.

I think I'm going to have to try a blasted long Case expression to parse up the time interval data item and wrap it in nice text of my own.  :(

Hi,

Are the source fields both definitely recognised as timestamps? Maybe you could try casting them to a Timestamp data type in the expression that subtracts them?

eg cast([first date], timestamp) - cast ([second date], timestamp)

Then in the format dialog, make sure the Format Type at the top is set to "Time interval". I just tried a similar thing in 11.2.4, and it works fine for me?

Cheers!

MF.