Conversion error

Hi,
I have a table called stats  that has the following columns:
jobname, ahours, aminutes, aseconds, amilliseconds.
I need to get an average of each job ran but I need to be able to
concatenate ahours, aminutes, aseconds, amillisecons into one column.
I am trying to run a simple query:
SELECT JobName,  CONVERT(NVARCHAR (100),(AVG (AHours) + ':' + AVG
(AMinutes) + ':' + AVG (ASeconds) + ':' + AVG (AMilliseconds))) AS
'Average Time' FROM stats
GROUP BY JobName
I am getting the following error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ':' to data type
int.

What I am doing wrong here?

Thank you,
T.


0
tolcis
6/7/2010 8:54:16 PM
sqlserver.server 1327 articles. 0 followers. Follow

4 Replies
936 Views

Similar Articles

[PageSpeed] 38

tolcis (nytollydba@gmail.com) writes:
> I have a table called stats  that has the following columns:
> jobname, ahours, aminutes, aseconds, amilliseconds.
> I need to get an average of each job ran but I need to be able to
> concatenate ahours, aminutes, aseconds, amillisecons into one column.
> I am trying to run a simple query:
> SELECT JobName,  CONVERT(NVARCHAR (100),(AVG (AHours) + ':' + AVG
> (AMinutes) + ':' + AVG (ASeconds) + ':' + AVG (AMilliseconds))) AS
> 'Average Time' FROM stats
> GROUP BY JobName
> I am getting the following error:
> 
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value ':' to data type
> int.
> 
> What I am doing wrong here?
 
SQL Server has a strict type precedence, which says that whenever two
data types meet in an expression, a type with lower precedence gets
converted to the type with the highest precedence. (Provided that an
implicit conversion exists at all.)

Strings are low in that precedence list, and numbers are higher. So 
above the ':' gets implicitly converted to int, which of course
fails. You would need to convert each value to varchar before doing
the concatenation.

Then again, I don't think your expression very correct anyway. Consider
this instances of elapsed time in minutes and seconds:

  1:01
  1:00
  0:59

AVG(Minutes) is 1, but what is AVG(Seconds)?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/7/2010 9:44:25 PM
On Jun 7, 5:44=A0pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> tolcis (nytolly...@gmail.com) writes:
> > I have a table called stats =A0that has the following columns:
> > jobname, ahours, aminutes, aseconds, amilliseconds.
> > I need to get an average of each job ran but I need to be able to
> > concatenate ahours, aminutes, aseconds, amillisecons into one column.
> > I am trying to run a simple query:
> > SELECT JobName, =A0CONVERT(NVARCHAR (100),(AVG (AHours) + ':' + AVG
> > (AMinutes) + ':' + AVG (ASeconds) + ':' + AVG (AMilliseconds))) AS
> > 'Average Time' FROM stats
> > GROUP BY JobName
> > I am getting the following error:
>
> > Msg 245, Level 16, State 1, Line 1
> > Conversion failed when converting the varchar value ':' to data type
> > int.
>
> > What I am doing wrong here?
>
> SQL Server has a strict type precedence, which says that whenever two
> data types meet in an expression, a type with lower precedence gets
> converted to the type with the highest precedence. (Provided that an
> implicit conversion exists at all.)
>
> Strings are low in that precedence list, and numbers are higher. So
> above the ':' gets implicitly converted to int, which of course
> fails. You would need to convert each value to varchar before doing
> the concatenation.
>
> Then again, I don't think your expression very correct anyway. Consider
> this instances of elapsed time in minutes and seconds:
>
> =A0 1:01
> =A0 1:00
> =A0 0:59
>
> AVG(Minutes) is 1, but what is AVG(Seconds)?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.msp=
x

Well, we have some of the jobs that run only couple of seconds or some
run milliseconds.  I need to get average on all of them.  How would I
accomplish this in a single select?
Thank you,
T.
0
tolcis
6/8/2010 1:13:37 PM
tolcis (nytollydba@gmail.com) writes:
> Well, we have some of the jobs that run only couple of seconds or some
> run milliseconds.  I need to get average on all of them.  How would I
> accomplish this in a single select?

convert(
   dateadd(ms, 
        AVG(Hours*3600*1000�+ Minutes*60*1000 + Seconds*1000 + 
            Milliseconds),
       '19000101'), 108)




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/8/2010 10:07:36 PM
Good anwser Erland!

To explain.

Erland is taking the total time and converting to milliseconds, finding the
average time and then converting the answer back to Hours,Mins,Secs,Milsecs


Erland Sommarskog wrote:
>> Well, we have some of the jobs that run only couple of seconds or some
>> run milliseconds.  I need to get average on all of them.  How would I
>> accomplish this in a single select?
>
>convert(
>   dateadd(ms, 
>        AVG(Hours*3600*1000´+ Minutes*60*1000 + Seconds*1000 + 
>            Milliseconds),
>       '19000101'), 108)
>

-- 
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1

0
obiron
6/11/2010 12:40:20 PM
Reply:

Similar Artilces:

Error Windows Update C800044E
No puedo descargar actualizaciones de Update ni de Defender. Utilizo Wnidows Vista 32 bits y sale este error y windowsupdate_dt000 Hola Francisco, Microsoft patrocina los foros de discusión para a todos aquellos de habla hispana para información relevante, dudas y temas referentes a participar e intercambiar ideas entre sí. Aqui te pongo estos 2 URL's http://social.answers.microsoft.com/Forums/es-ES/> http://social.technet.microsoft.com/Forums/es-ES/> Buena suerte -=- "Francisco" wrote: > No puedo descargar actualizacion...

SBS 2008
Hi, We've installed (and integrated into the console) the SBS Best Practice Analyser. SBS 2008 Standard. Our console shows this message for the server: The Best Practices Analyzer has completed a scan of your system and has found errors in your system configuration. For detailed information about the scan errors, manually run the Best Practices Analyzer tool. Error Count: 1 but whenever we manually run it, we get no errors. Any ideas? Or where to start looking? Jim ...

Cash Payment Voided in Error
I have a user who inadvertently voided a receivables cash payment . The invoice the payment was applied to is also voided. The payment she intended to void still shows as 'Open'. The wrong void shows in the GL. The customer's account should show a credit balance. Is there a way to undo the payment that was voided? what is the best way to fix? Thanks, There is no undoing a void. To 'fix' this you can re-enter the voided payment, you'll have to put a '-A' or '.' at the end of the check number, as GP will not let you receive the same check nu...

'(0x80070057)synchronising headers',getting this error in outlook
ive just installed my outlook and have set it up to retrieve mail from 2 eircom accounts but when i try to send and receieve i get the following message: Task 'joanne - Sending' reported error (0x80070057) : 'Could not complete the operation. One or more parameter values are not valid.' Task 'joanne: Folder:Inbox Synchronizing headers.' reported error (0x80070057) : 'An unknown error has occurred. Please save any existing work and restart the program.' ...

Error Send mail
No se puede localizar a los destinatarios siguientes: ventas_gloablexpress@terra.com.pe en 29/09/2006 12:03 p.m. Problema de comunicación de SMTP con el servidor de correo electrónico del destinatario. Póngase en contacto con el administrador del sistema. <neptuno.ilg-peru.com #5.5.0 smtp;550 User unknown> ...

Conversions
Hello! I work as an Information Specialist. Majority of our job requests involve conversions. We just received an Adobe Acrobat pdf in which the original document was created in MAC OS. The conversion was successful through Omni Page as we only needed to capture text to convert to a Word document. I was wondering is there any other suggestions you can recommend for conversions from other softwares such as MAC. Thanks so much for your time. A PDF file is a PDF file, no matter which OS was used when the file was created. If you want conversions for specific Mac formats t...

Postcard conversion...
I have been printing 5 1/2 x 8 1/2 cardstock postcards and would now like to convert my 2 per page size to 4 per page or 4 x 6 size postcards. How can I convert my larger graphics and text w/o having to go thru the madness of cutting, pasting, and resizing everything to the smaller size postcard? Thanking you in advance. Frank Engels ...

Reporting Error
This error is prompting when i am trying to access the Reports tag in the crm externally. Unable to find part of the path:"C:\Program Files\Microsoft CRM\CRMWeb\ReportServer\ReportServices.asmx." Please Any Suggestions? ...

data dictionary memory allocation error
I carried out an upgrade v7.5 based on c-tree to 7.5 based on msde but this did not go flawlessly. Now when I run great plains I get the error: "data dictionary memory allocation error" I need help to be able to move from here because I need to upgrade to version 9 and to do that I will need version 7.5 to be working well. I have edited the Dynamics.set file according to help from the forums but still get the same error. Thanks Mugabi I have had this problem when I have tried to delete a product from the Dynamics.set file. I would do a new install of the Dynamics client, i...

VC6 -> VC2005 conversion problem
Hello, I'm converting my project from VC++6 to VC++2005. among many compilation errors now solved, that one is stucking me : I have that template : template <class T> class TLineApproximator { public: //! \name Structures and typedefs //@{ //! 2D homogenous point struct SHomog { public: SHomog(T _x=0, T _y=0, T _w=1) { x=_x; y=_y; w=_w;}; T x; T y; T w; }; //! 2D point struct SPoint { public: SPoint(T _x=0, T _y=0) { x=_x; y=_y;}; static void CrossProduct( const SPoint& p, const SPoint& q, SHomog& r) { r.w = p.x * q.y - p.y * q.x; r.x...

Toolbar resizing error
I have a project built in MFC using Visual c++ 6.0. The main window contains a rebar with 2 CToolbar bands created like so: m_wndToolBar.CreateEx(this, TBSTYLE_TRANSPARENT | TBSTYLE_FLAT); For *some* reason, if you rclick on the windows desktop and select properties, change nothing and click ok, the toolbar buttons increase in size from the standard 16X15 to 22X21. I am never changing the size of the bars anywhere in my own code nor am I using a derived toolbar class. Does anyone have a clue what might cause this or at the very least can point out what messages would be sent when the display...

Disable Outlook 2002 pop server error messages?
I have Outlook 2002 (SP2) running on a XP Pro box. This PC is my "server" PC, ie Outlook runs continuously and does a send/receive every minute. The main pst file is shared using Outlook Folders, see: http://www.outlookfolders.biz/Lang/English/OLWorkgroupFolders/olw1.htm However, whenever there is an email problem whether it be with the ISP or something else, Outlook pops up an error message which until cleared prevents any further email being sent/received. Is there any way that this error box can be disabled or made so that it doesn't grab the focus and thus prevent Outlook fr...

Error installing SRS
HI Installing SRS manually (as CRM 3 fails on install to SBS) and get error 1603 towards end of install. DB's are installed but virtual directories aren't. Anybody out there got anything useful. Regards Pete. What is the exact error? "PeteC" wrote: > HI > > Installing SRS manually (as CRM 3 fails on install to SBS) and get error > 1603 towards end of install. DB's are installed but virtual directories > aren't. Anybody out there got anything useful. > > Regards > Pete. Have you seen the Article ID 867872. I received a similar erro...

why error?
Hi all, The lines below cause my macro to halt with the error message : "error determined by application" (or something like that, I translated from Dutch). What could be wrong? When I put the formula in the normal way in cell L1 it works fine. Range("L1").Select ActiveCell.FormulaR1C1 = "=""aantal cursisten = ""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1" Jack Sons The Netherlands It works fine for me. Is your sheet protected? In article <uWt867WmEHA.3632@TK2MSFTNGP09.phx.g...

TYPE CONVERSION
My goal is to automatically update the End Date to display the create date from a record previously reported. For example the end date for the first record listed below should be 9/24/2009 12:41:00 PM and the end date for the third record listed below should be 8/21/2009 9:00:00 AM. ID CATEGORY RANGE CREATE_DATE END_DATE 3426 Service 24 months 9/15/2009 9:35:00 AM 3426 Service 24 months 9/24/2009 12:41:00 PM 3426 Service 36 months 8/20/2009 12:23:00 PM 3426 Service 36 months 8/21/2009 9:00:00 AM I have the query listed belo...

Check for well formedness and get all errors
Hi I would like to run through an XML file using C# 2.0 and check for well formedness - and I would like to get all errors and not jsut the first one. My code is, off course, very simple: XmlTextReader xtr = new XmlTextReader(xmlInstanceTextBox.Text); try { while (xtr.Read()) {} } catch (Exception e) { errors += e.Message; } finally { xtr.Close(); } When doing a validating reader, I can get an event everytime an exception occurs, and keep running. But this one... it seems that there is now way? Any thouhgts? Thanks in advance! eliasen wrote: > I would like to run through an XML file...

Printing Public Calandar Error
Just wondering if anyone else has had this error: When trying to print a public calendar in Outlook 2003 - daily style, (Exchange 5.5 Enterprise, but happened in Standard as well) my users get the following error: The folder cannot be opened because there is a configuration problem on the server. It will print out in weekly and monthly style though. Anyone have any ideas what I might have set up wrong? Thanks in advance. mike ...

It's me again!! Error message.
Ok...I asked for some help Sunday evening, and received some VALUABLE information which helped with my problem getting Excel to do what I wanted it to do. Now when I open my program this morning, and I'm getting an error message reading: "Macros are disable because the security level is set to High and a digitally signed Trust Certificate is not attached to the macros. To run the macros, change the security level to a lower setting (not recommended), or request the macors be signed by the author using a certificate issued by a Certificate Authority." I can click &quo...

M2004 Deluxe-TIRA to Roth Conversion Question
Hi, I am using MS Money 2004 Deluxe. I did a Traditonal IRA conversion to a Roth back on Nov 24th. The conversion was with the same mutual fund, ticker symbol. How do I do the transaction within Money to effectively close the TIRA and have the Roth Start on 11/24/08? I want the converted amount, for tax purposes, to show up on the Money Home page. Thanks! Transfer Out investment activity? "Mike" <Mike@nothere.com> wrote in message news:%23UAtIoDaJHA.3908@TK2MSFTNGP06.phx.gbl... > I am using MS Money 2004 Deluxe. I did a Traditonal IRA conversion to a > Roth b...

Conversations
I have searched for and got a list of 70 items in my inbox, which I now view as conversations, bbut the count at the bottom of the screen still shows '70 items'. Is there a quick and easy way to count the number of conversations, rather than the number of items? Thanks, G No. <george.walsh@doeni.gov.uk> wrote in message news:6798cc53-de91-4772-be4e-9de69dc45289@w40g2000hsb.googlegroups.com... >I have searched for and got a list of 70 items in my inbox, which I > now view as conversations, bbut the count at the bottom of the screen > still shows '70 items'. ...

OpenReport error handling
Hello. I have a form with parameters, so the user can enter the start date and end date and then click on Preview to run the report. The form has a Preview error handling code. If the form is closed without clicking preview, the form shuts nicely. However, I have this report set to run from an Option button on a switchboard form, that in itself has a Preview button: I click the option button, then click on the switchboard Preview button, the parameter form opens (where I could enter the parameter dates and click Preview to run the report- but here I don't), I close the parameter fo...

TransferSpreadsheet Error #2
I am using the DoCmd.TransferSpreadsheet method in an Access Form to export Data to an Excel workbook. Sometimes the method works, sometimes I get the following error: Error Number: 3310 Error Description: This property is not supported for external data sources or for databases created with a previous version of Microsoft Jet. The table being exported is actually a query, and the database has been created with the current version of Jet (4.0). Can anyone help me with this issue? Thanks, DJ Are there functions on the query? Are any properties set on columns of the queries, like date...

Publisher 2000 conversion to PDF file
I made up a business card in Publisher 2000 using a photo and some text. It looks great but when I try to convert it to a PDF file either by using in house with Docudesk or sending it out to Zamzar it comes back in a set up of 10 cards on a 8x11 page like I am going to print in on some Avery format. It must be a setting in my Publisher program but I do not know how to correct it. Please help What size are the cards? Four to a page? Are you using Acrobat? Are you changing the page settings in the print options? If you are trying to print four to a page, Print, Select the PDF printer...

conversion
Hello, I'm dealing with a newsletter in Publisher format that I'd like in text form (I do not care about the Publisher special formatting or graphics). The newsletter will be converted to PDF and made available on a website. I would like a text alternative. Thank you. es What's wrong with MS Word or even Open Office or Word Pad if you don't have Word? Once your newsletter is created in Word or other text editor (strictly line text according to your post) print to a PDF creator, of which there are some free ones on the internet. "esha734" <esha734@discu...

conversions
Hi: Does anyone know or if it possible to convert a Excel file to a PDF format? Thanks, Lisa Yes, you would need a conversion program like Adobe which will install a printer driver, then in excel you select file>print and change the default printer to adobe's driver. I have paperport 9.0 deluxe which also can install a pdf printer driver.. -- Regards, Peo Sjoblom "Lisa Murray" <lmurray@novamachine.com> wrote in message news:uKIeVLRrDHA.1724@TK2MSFTNGP10.phx.gbl... > Hi: > > Does anyone know or if it possible to convert a Excel file to a PDF format? &g...