x

Average of a date in SQL

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'  
more ▼

asked Nov 14, 2012 at 08:52 PM in Default

analystpro gravatar image

analystpro
20 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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'
more ▼

answered Nov 14, 2012 at 09:20 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.1k 47 49 76

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, 2012 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, 2012 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, 2012 at 09:58 AM Grant Fritchey ♦♦

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

Nov 15, 2012 at 12:36 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28

asked: Nov 14, 2012 at 08:52 PM

Seen: 900 times

Last Updated: Nov 15, 2012 at 12:36 PM