Changing int to Time from a DateDiff

Question

I have 3 times which I am putting onto a bar graph but I cant work out how to change the AvgUserSegmentTime to a Time format (00:00:00.00). Right now I'm getting Fastest = 00:00:00.01 Slowest = 00:00:24:22.22 and Avg = 61 which means the avg on a bar graph is much bigger. any idea on how to change the int to a time?

SELECT  FastestSegmentTime , SlowestSegmentTime,  
 (SELECT AVG(DateDiff(SECOND, tblTrace.trStart , tblTrace.trFinish)) 
    FROM tblTrace
   INNER JOIN 
         tblUsers 
      ON usrID = tr_usrID
   WHERE tblTrace.trFinish IS NOT NULL 
     AND tblTrace.trObjectType LIKE 'Segment%' 
     AND tblTrace.tr_vnuID = @vnuID 
     AND tblTrace.trStart BETWEEN @StartDate AND @EndDate 
     AND tblUsers.usrEmail NOT LIKE '%@test%' ) as AvgSegmentTime
    FROM ( 
      SELECT MIN(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME(2))) AS FastestSegmentTime,  
             MAX(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME(2))) AS SlowestSegmentTime            
        FROM tblTrace 
       INNER JOIN 
             tblUsers 
          ON usrID = tr_usrID
       WHERE tr_vnuID = @vnuID 
         AND trFinish IS NOT NULL 
         AND tr_usrID IS NOT NULL 
         AND trObjectType LIKE 'Segment%'  
         AND trStart BETWEEN @StartDate AND @EndDate 
         AND tblUsers.usrEmail NOT LIKE '%@test%'
) coreData

score 1 · Answer 1 · answered Nov 27 '13 at 14:14

try to add the average datediff to 0 like this:

SELECT  FastestSegmentTime , SlowestSegmentTime,  
 (SELECT dateadd(second, AVG(DateDiff(SECOND, tblTrace.trStart , tblTrace.trFinish)),0) as AVGSegmentTime
    FROM tblTrace
   INNER JOIN 
         tblUsers 
      ON usrID = tr_usrID
   WHERE tblTrace.trFinish IS NOT NULL 
     AND tblTrace.trObjectType LIKE 'Segment%' 
     AND tblTrace.tr_vnuID = @vnuID 
     AND tblTrace.trStart BETWEEN @StartDate AND @EndDate 
     AND tblUsers.usrEmail NOT LIKE '%@test%' ) as AvgSegmentTime
    FROM ( 
      SELECT MIN(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME(2))) AS FastestSegmentTime,  
             MAX(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME(2))) AS SlowestSegmentTime            
        FROM tblTrace 
       INNER JOIN 
             tblUsers 
          ON usrID = tr_usrID
       WHERE tr_vnuID = @vnuID 
         AND trFinish IS NOT NULL 
         AND tr_usrID IS NOT NULL 
         AND trObjectType LIKE 'Segment%'  
         AND trStart BETWEEN @StartDate AND @EndDate 
         AND tblUsers.usrEmail NOT LIKE '%@test%'
) coreData

You might use `Cast('00:00:00.00' as Time)` instead of `0` as third argument for `DateAdd` — bummi, Nov 27 '13 at 14:24

score 1 · Accepted Answer · edited May 23 '17 at 10:25

Personally I'd start by using grouping to make it more efficient (this part is optional of course) but your problems are occurring because it is averaging the seconds but reporting the others as time. Using the following answer you can convert between seconds and time allowing you to compare accordingly, alternatively you could convert your former columns to seconds.

SELECT  
    FastestSegmentTime = MIN(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME(2)))
    , SlowestSegmentTime = MAX(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME(2)))
    , AvgSegmentTime = DATEADD(ms, 1000 * AVG(DATEDIFF(SECOND, tblTrace.trStart , tblTrace.trFinish)),0)
FROM 
    tblTrace
    INNER JOIN tblUsers ON usrID = tr_usrID
WHERE 
    (tblTrace.trFinish IS NOT NULL)
    AND (tblTrace.trObjectType LIKE 'Segment%') 
    AND (tblTrace.tr_vnuID = @vnuID) 
    AND (tblTrace.trStart BETWEEN @StartDate AND @EndDate)
    AND (tblUsers.usrEmail NOT LIKE '%@test%')

Changing int to Time from a DateDiff

2 Answers2