1

I have a table with a column, Duration that has value in seconds.

In Visual Studio / SSRS 2016 I want to display it in HH:MM:SS format.

I have tried to use the following expressions

=Format(DateAdd("s", Fields!Duration.Value, "00:00:00"), "HH:mm:ss")

But it didn't seem to work correctly, for example, 1800s will be displayed as 01:38:00

Can anybody see the problem?

Thanks!

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
Olivia
  • 45
  • 6
  • I've just created a simple report and your expression works as I would expect. i.e. 1800 was returned as 00:30:00 . What datatype is the duration column in your dataset? – Alan Schofield Feb 10 '20 at 15:22
  • @AlanSchofield Hi thx for your reply, the datatype is INTEGER – Olivia Feb 10 '20 at 15:40
  • Are you sure that the value is 1800 ? It should work as you have it.Add another textbox with the expression `=Fields!Duration.Value` and make sure it returns what you expect. I expect it will not. If the scope of the textbox is an aggregation such as `=SUM(Fields!Duration.Value)` then you will need to do the same within you conversion expression. – Alan Schofield Feb 10 '20 at 15:52
  • @AlanSchofield it has worked now, I should have applied this expression on the ''Value'' property, but instead I tried to change the number format. – Olivia Feb 10 '20 at 16:00
  • Thanks for your time! But I still have a problem here, the textbox display the number as I wished in hh:mm:ss format, but the chart which was generated from this table still display 1800s, what should I do now? – Olivia Feb 10 '20 at 16:01
  • You need to start a new question specifically about this problem. It's best to show a sample of your data, what you get now and what you expect it to look like based on your sample data. If you do this people will help you faster. – Alan Schofield Feb 10 '20 at 16:04

1 Answers1

0

To convert a number of seconds to time format use the TimeSerial function:

=Format(TimeSerial(0,0,Fields!Duration.Value),"HH:mm:ss")

The problem with your way might be that it is not implicitly converting from the text string to time format as you expect, so the following may also work:

=Format(DateAdd("s", Fields!Duration.Value, TimeValue("00:00:00")), "HH:mm:ss")
RET
  • 861
  • 6
  • 15
  • I have tried this too, but it has also returned 01:38:00 – Olivia Feb 10 '20 at 15:42
  • Try forcing the duration to integer with CINT – RET Feb 10 '20 at 15:44
  • If that still doesn't work, add the duration in the same scope and check it is returning the value you believe it is. – RET Feb 10 '20 at 15:47
  • it is working now, I should have applied this expression on the ''Value'' property, but instead I tried to change the number format. sorry for my stupid question and thx for your help! – Olivia Feb 10 '20 at 16:02