Conerting a DateDiff Average time from an int to HH:mm:ss

Question

Post reply

Conerting a DateDiff Average time from an int to HH:mm:ss

mnut

Old Hand

Points: 305
More actions
November 22, 2011 at 6:03 am

#252325

Hi,
In my SQL query I create datediff column. In SSRS I get the average value from this. This brings back an integer. I can break it down to seconds, but when I format the textbox it comes back with HH:mm:ss as the value. When I convert it back to minutes it creates 2.36 which is actually 141 seconds or 2.21 mins.
This my current code:
=Avg(Fields!QTime.Value)*60
How do I get around this problem and express 2.36 as actually 2 mins and 21 seconds?
Mike

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

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

Lowell SSC Guru Points: 323753 More actions · Answer 1

i'm sure there are other ways, but here's one way:

--3 hrs 55 min 12 secs

/*

----------- ----------- ----------- -------

3 55 12 3:55:12

*/

declare @secs int

SET @secs=14112

SELECT @secs / 3600 ,

(@secs % 3600) / 60 ,

@secs %60 ,

convert(varchar,@secs / 3600) + ':' + convert(varchar,(@secs % 3600) / 60) + ':' + convert(varchar, @secs %60)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mnut Old Hand Points: 305 More actions · Answer 2

Hi Lowell,

I was hoping to do this in actually in SSRS expression builder. Is this not possible?

Mike

Lowell SSC Guru Points: 323753 More actions · Answer 3

mnut (11/22/2011)
Hi Lowell,
I was hoping to do this in actually in SSRS expression builder. Is this not possible?
Mike

of course; the math is the same...integer math or modulus;

something like

CINT(Field!Value / 3600) & ":" & CINT(Field!Value MOD 3600) / 60) & ":" & CINT(Field!Value MOD 60)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mnut Old Hand Points: 305 More actions · Answer 4

Lowell you are a STAR!

Used your idea and came up with this

=Cint((Avg(Fields!QTime.Value)*60)/3600)&":"

& cint((Avg(Fields!QTime.Value)*60)/60)&":"&

(cint(Avg(Fields!QTime.Value)*60)-(cint((Avg(Fields!QTime.Value)*60)/60)*60))

and it works!

Cheers.

Mike

TomThomson SSC Guru Points: 104768 More actions · Answer 5

There's another way (not particularly efficient):

select substring(convert(dateadd(SS, <seconds>,'2000-12-31T00:00:00'),126),15,5). That will give you 02:21 for 141 seconds; if you want to change the : to some other string and add seccs on the end you can do something like

select replace(

substring(convert(dateadd(SS, <seconds>,'2000-12-31T00:00:00'),126),15,5) +' secs',

':',' mins '). That will give you "02 mins 21 secs" for 141.

Change 15,5 to 12,8 if the result may be more than an hour. Then for 3741 seconds you will get 01:02:21. The replace gets a little more complicated as you may want the two colons transformed differently from each other.

If you want times longer than an hour given as minutes and seconds (eg 62:21 rather than 01:02:21) then adapt the way Lowell described.

Tom