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

avatar 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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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.

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:

x38

asked: Nov 14, 2012 at 08:52 PM

Seen: 1062 times

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

Copyright 2016 Redgate Software. Privacy Policy