if time is more than 60 minutes then show in hours in sql server

Question

I have a stored procedure like :

ALTER procedure [dbo].[IBS_Podiumsummryaveragetime]
@locid integer=null
as 
begin
set nocount on;
select  avg( datediff(mi,t.dtime, t.DelDate )) as Avgstaytime,
 avg( datediff(mi,t.dtime, t.PAICdate )) as  Avgparkingtime, 
 avg( datediff(mi,t.Paydate, t.DelDate )) as AvgDelivarytime 
 from (select top 10 * from transaction_tbl where locid=@locid and dtime >= getdate()-7 order by transactID desc ) t  
 end

if average time i am getting more than 60 minutes then i want to show in hours..how i can do this?
any help is appreciable??

answered before here: http://stackoverflow.com/a/19298717/3248346 — , Mar 30 '14 at 12:55
Can you show sample output for what you want? I mean, do you want some in hours and some in minutes? That seems like a bad idea. — Gordon Linoff, Mar 30 '14 at 12:55
now result is getting in minutes..if the result is more than 60 minutes,,then i want show that result in hours,,that s all — user3106114, Mar 30 '14 at 12:58
why don't you use a case statement, if mins is greater than 60 does query X if less than 60 do Y. — , Mar 30 '14 at 13:13

score 1 · Accepted Answer · 2014-03-30T19:09:34.670

1

I couldn't type this in the comments so I've added it as an answer:

SELECT 
   CASE 
      WHEN avg( datediff(mi,t.dtime, t.DelDate )) <= 60 
          THEN CAST(avg( datediff(mi,t.dtime, t.DelDate )), varchar)
      ELSE 
          CAST(avg( datediff(mi,t.dtime, t.DelDate )) / 60, varchar) + ":" + CAST(avg( datediff(mi,t.dtime, t.DelDate )) % 60, varchar)
   END

edited Mar 30 '14 at 19:09

answered Mar 30 '14 at 13:32

I'd prefer an ELSE to the 2nd WHEN condition. – Karl Kieninger Mar 30 '14 at 13:46
@KarlKieninger, I agree. Code modified. – Mar 30 '14 at 13:47
sir i am searching for conversion code..then i will let u know wethar this is working fine or not – user3106114 Mar 30 '14 at 13:55
Your answer to @GordonLinoff in the question comments wasn't clear. How do you want to display hours? i.e. is 61 minutes 1, 1:1, 1.017? is 90 minutes 1, 1.5, 1:30, 2? – Karl Kieninger Mar 30 '14 at 14:00
if 61 minutes then i want to show 1:1 Min,,if 90 minute then i want to show 1:30 ,,if 130 minute then i want to show 2:10 like that – user3106114 Mar 30 '14 at 14:04
I've modified the query above. Please check the syntax, but you get the idea. – Mar 30 '14 at 14:36

Karl Kieninger · Answer 2 · 2014-03-31T12:58:04.627

1

Variations on @I.K.'s theme's (which I up voted)

SELECT CASE WHEN avg(datediff(mi,t.dtime, t.DelDate)) > 60 
            THEN CAST(avg(datediff(mi,t.dtime, t.DelDate))/60 AS VARCHAR(3)) + ':' 
            ELSE ''
       END
       + CAST(avg(datediff(mi,t.dtime, t.DelDate)) % 60  AS VARCHAR(2)) 

--OR

SELECT CASE WHEN avg(datediff(hour,t.dtime, t.DelDate)) > 0 
            THEN CAST(avg(datediff(hour,t.dtime, t.DelDate)) AS VARCHAR(3)) + ':' 
            ELSE ''
       END
       + CAST(avg(datediff(mi,t.dtime, t.DelDate)) % 60  AS VARCHAR(2))

edited Mar 31 '14 at 12:58

answered Mar 30 '14 at 14:37

Karl Kieninger

8,841
2
33
49

I'm upvoting, because you put lengths in for `varchar()`. – Gordon Linoff Mar 30 '14 at 21:46
but one problem,,in the case of minutes,,if example if the average time is only 5 minutes,,then it s showing null. – user3106114 Mar 31 '14 at 05:55

if time is more than 60 minutes then show in hours in sql server

2 Answers2