Convert for hh:mm:ss

Question

Post reply

Convert for hh:mm:ss

Bob Boursaw

SSC Eights!

Points: 865
More actions
April 15, 2008 at 12:13 am

#189105

Is there a built in function / expression in 2005 reporting services that will allow you to convert x number of seconds stored in a DB to an hh:mm:ss format?
I have been searching all over and it does not appear that such an expression exists.
If not, would the appropriate action be to create a new dataset within my report and then include the SQL or stored proc containing the necessary code to put that information together and utilize that within my report?
Bottom line, I have a column in my table which stores the number of seconds something took and in my report I would like to display 00:02:04 to the reader (as an example)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SELECT CONVERT(CHAR(8), DATEADD(SECOND, @Seconds, '00:00:00'), 108)

N 56°04'39.16"
E 12°55'05.25"

Bob Boursaw SSC Eights! Points: 865 More actions · Answer 2

The SQL works perfectly, thank you very much. Do you know how I can incorporate this in to an expression within reporting services? It will not except the Convert function as being valid within the expression builder for the field I'm working with.

Jurgen-666687 Valued Member Points: 65 More actions · Answer 3

Try using the following straight in you dataset for your report:-

SELECT CONVERT(CHAR(8), DATEADD(SECOND,convert(int, @Seconds), '00:00:00'), 108)

Had to convert the 2nd DATEADD argument to INT else an error was thrown

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 4

I use a function like this for general interval timing in TSQL...

If Object_Id('TimeSpan','fn') is not Null Drop Function TimeSpan

Go

Create Function TimeSpan

(

@StartDT DateTime,

@EndDT DateTime

)

Returns VarChar(16)

As Begin

Declare @s-2 VarChar(64),@s1 VarChar(64),@s2 VarChar(64),@i Int

Select @s-2=Convert(VarChar(256),DateAdd(ms,DateDiff(ms,@StartDT,@EndDT),'00:00:00'),121),

@s1=Left(@s,10),@i=SubString(@s,12,2),@s2=Right(@s,9)

Return Cast(DateDiff(day,@StartDT,@EndDT)*24+@i as VarChar)+':'+@s2

End

Go

Declare @s-2 DateTime,@e DateTime

Set @s-2='2008-04-10 08:00:00AM'

Set @e='2008-04-12 02:23:45.16PM'

Print dbo.TimeSpan(@s,@e)

Will return the following time span: 54:23:45.160

That's Hours:Minutes:Seconds.MilliSeconds

Tastes great, less filling...:)

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Bob Pinella SSCommitted Points: 1737 More actions · Answer 5

You can use the following to achieve what you are looking for:

=Format(DateAdd("s",12600, Today), "hh:mm:ss")

12600 would be the number of seconds from your database and in this case would result in the output 03:30:00.

Note that the Today() function ignores time and outputs 12:00:00 AM by default so it does not matter when you run this function.

Bob Pinella