Average of a date in SQL

Question

The code snippet below calculate time difference between 2 dates. I have to find the Average of the time it takes to process a few records. When I inserted the AVG word prior to the DATEDIFF, I get an error "conversion failed when converting varchar value ':' to data type int" Any idea why ? thanks

RTRIM(AVG(DATEDIFF(second, ale1.eventdate, ale3.eventdate) / 3600)   
      + ':' + RIGHT('0'+RTRIM((DATEDIFF(second, ale1.eventdate, ale3.eventdate) % 3600) / 60),2) 
      + ':' + RIGHT('0'+RTRIM((DATEDIFF(second, ale1.eventdate, ale3.eventdate) % 3600) % 60),2))  
      AS 'Process Time'

Answer 1

0

A combination of missing parentheses, and also you need to add the avg function to each datediff

RTRIM(avg(DATEDIFF(second, ale1.eventdate, ale3.eventdate) / 3600))
+ ':' 
+ RIGHT('0'+RTRIM(avg(DATEDIFF(second, ale1.eventdate, ale3.eventdate) % 3600) / 60),2)
+ ':' 
+ RIGHT('0'+RTRIM(avg(DATEDIFF(second, ale1.eventdate, ale3.eventdate) % 3600) % 60),2)
AS 'Process Time'

answered Nov 14 '12 at 09:20 PM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

Whoo hoo! 40K Well done. And it was my vote that put you over. (and yes, I know, you're at 39,950, but still).

Nov 14 '12 at 11:51 PM Grant Fritchey ♦♦

Cheers Grant! This last couple of K have been a slog..... I need to put more effort in.

Nov 15 '12 at 08:35 AM Kev Riley ♦♦

You and me both. I keep thinking I might make 50k, but I'm not sure how.

Nov 15 '12 at 09:58 AM Grant Fritchey ♦♦

so, after my vote you need 5pts :) Congrats Kev.

Nov 15 '12 at 12:36 PM Fatherjack ♦♦

(comments are locked)

Average of a date in SQL

Follow this question

Related Questions