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
1330 Views

Similar Articles

[PageSpeed] 15

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:

Name could not be resolved error.....
Hi Have a strange problem, where I can set up mail accounts, and use these fine, but if I try to add a mailbox in the Open These Additional Mailboxes field, I immediately get an error "The name could not be resolved. The action could not be completed" This is before ive even tried to resolve a name, its as I click Add, the error just appears, so you dont even get a chance to put a name in there. Any ideas what could have caused this? it seems to be after we ran the move server wizard and moved the server with these accounts on into a new site. Thanks Graham ...

SOP Document Lookup Error
Whenever we try using the document number lookup on the Sales Transaction Entry screen in GP8, we get the following message once we click the magnifying glass: Unhandled script exception: Index 0 of array 'Type Name' is out of range in script 'Document_Scroll SCROLL_FILL'. Script terminated. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE SCRIPT_CMD_INDEX Does anyone have any ideas on how to clear this up? This pops up as soon as you click on the magnifying glass, then once you click ok, it pops up 2 more times. Do a query against table SOP10100 and look for any values in the SOP...

error 80070103 after trying to install my saitek cyborg x joystick
i have tried installing my saitek cyborg x joystick several different ways - install cd, "plug and play" while getting the drivers from microsoft and the saitek websites and have had no luck. still getting the "error 80070103" message. help? Tip: Do not install any driver updates offered by Windows Update. Get them from the manufacturer's website instead. http://www.saitek.com/uk/down/drivers.php "gabriel_blues" wrote: > i have tried installing my saitek cyborg x joystick several different ways - > install cd, "plug and play" ...

Microsoft CRM 3.0 (KB922815) Installation Error
When i try to install Update Rollup 1 for MSCRM 3.0 i get following error. Setup could not find Microsoft CRM 3.0 Server or you have a mismatch between update language and Microsoft CRM language. The installation of this update has stopped. Is this rollup language dependent? I have a swedish installation of MSCRM. Thanks! // Mats ------=_NextPart_0001_11F19277 Content-Type: text/plain Content-Transfer-Encoding: 7bit Yes this rollup is language dependent. You need to install SVE(swedish - sweden) UR1 package. Thanks, Indira Dhingra This posting is provided "AS IS" with no...

MSEchangeAL 8270 Error
Dear all, we receive on the central exchange server running the offline Addressbook generation the following error. Event Type: Error Event Source: MSExchangeAL Event Category: LDAP Operations Event ID: 8270 Date: 24.11.2005 Time: 13:24:02 User: N/A Computer: XYZMAIL01 Description: LDAP returned the error [35] Unwilling To Perform when importing the transaction dn: <GUID=055AE52D-27B9-44E5-A05F-A1BF32881EA9> changetype: Modify member:delete:<guid=2e0483a2-cbb5-4ce7-8173-2842a90cea4e> - DC=XYZ,DC=com We are running a 2003 forest with Exchange 2003. The forest has severa...

MS Visual Basic error in Outlook
Vba Project OTM - Thisoutlooksession(code) window pops up, and I have to stop the degugger to send an email. I've been using OL 2002 for 2 year, never had this problem, How can I fix it ? "XP User" <my_email@is.private.com> wrote in message news:sb27g.4$RO4.3@fe12.lga... > Vba Project OTM - Thisoutlooksession(code) > window pops up, and I have to stop the degugger > to send an email. > > I've been using OL 2002 for 2 year, never had this problem, > > How can I fix it ? Fix the code ? Supply more info so we have a better idea what your problem...

80070490 Error: Windows update encountered an unknown error 03-04-10
3 security updates will not complete (now 4). Error Code 80070490 02-Mar-10 I am unable to do these three security updates: KB970238(release date 6/9/09), KB97451(release date 10/13/09), KB954155(release date 10/13/09), & KB975517(release date 10/13/09). The last succesful update was KB9937286 on 2/27/2010 I have a Compaq Presario PC: SR5214X. OS Name Microsoft® Windows Vista™ Home Basic Version 6.0.6002 Service Pack 2 Build 6002 OS Manufacturer Microsoft Corporation System Manufacturer Compaq-Presario System Model GV441AA-ABA SR5214X System Type X86-based PC ...

Resource = rcreport. Error = 2109
I am using MS Money 2007 Deluxe on Windows XP Professional. All patches and updates are current on the machine. My MS Money file is 57,800KB. I have 5 customized reports, until just recently all custom reports worked, now the account balances (customized) and the account balances (standard) report will not load and the custom report returns the error in the subject line. I have made back up copies and done a standard repair to the file and reset all report to default settings and the account balances still will not load. Any suggestions? v/r Dave I am having the same problem. I...

error opening ppt file
I have office 2007 installed but when i try to open a ppt file created by some one it says powerpoint can't read the outline . No text converter is installed for this file type. karan In article <a888f8cd-1391-4124-80c0- 631197290d7a@y28g2000prd.googlegroups.com>, Guddu wrote: > I have office 2007 installed but when i try to open a ppt file created > by some one it says powerpoint can't read the outline . No text > converter is installed for this file type. Have a look here: "PowerPoint can't open the type of file represented by [FILENAME]"...

Strange Error (CRM 1.2)
Hi, I have a Virtual PC configuration set up for CRM demos. I have one image that has CRM 1.2 under Windows 2003 server. It is all configured and setup and running fine. I have another image with Windows XP configured. The two images see each other and communicate. From my Windows XP image I can access CRM both via browser and Outlook Client. However, occassionally I can't access the system via Outlook and when I try it via the browser I get a screen coming up with "Sonic Wall" on it asking for a login and password. If I enter in "Administrator" for the login...

Page not found error when opening business portal
Hi We're trying to install Invoice Approvals and Accruals 8.0, sharepoint services sp2 and Business Portal 2.5 Feature Pack to work with Great Plains 8.0 We can install Sharepoint services and Invoice Approvals and Accruals ok and they work fine However, when we install Business Portal and browse to http://servername/sites/businessportal/ We receive a "page not found" error screen We've tried several things but nothing seems to work Sharepoint services is installed in server farm mode and has been configured correctly (as far as I can tell) Any help would be great...

MS CRM 4.0 Rollup 2 error Data ProductInfo.ExistingLanguage is not available
Recently upgraded to CRM 4.0. Details are as follows - CRM is on premise - 60 Outlook clients - 32 bit server w/ SRS and Exchange router on CRM server - 64 bit DB CRM 4.0 upgrade goes well. Server application of Rollup 2 works fine. Most of my 60 Outlook clients are getting a popup upon running the rollup 2 .EXE: "Data ProductInfo.ExistingLanguage is not available" These are all US English installs (language id 1033). This is not a multi-language install. All users were made "local admins" for the purpose of the client upgrade to 4.0 as well as the rollup install. About...

Run-time error '381' Invalid property array index
Dear All, I am using RMS 2.0, In POS i set security (disable read) on all columns of setting Customer window. But i got this error when i press F7: Run-time error '381' Invalid property array index. Any kind of help will be highly appreciated. Ibrahim Zubair I get the same message when I try to reprint a receipt did you find the answer to this problem? "Ibrahim Zubair" wrote: > Dear All, > > I am using RMS 2.0, In POS i set security (disable read) on all columns of > setting Customer window. But i got this error when i press F7: > Run-time error '...

A SQL error has occurred. Going off line with Outlook CRM 4.
I have seen this in multiple workstations. Running the diagnostics, does not report any errors, however removing the temprary data under advenced fixes the problem by deleting the content of the off line database. The problem with this fix is that it may take up to 12 hours for some of our overseas employees to do their initial off line. When is SP1 due? ...

Not able to view activities
Hi, I have a MS CRM 3.0 VPC running. I have allocated 1GB memory as recommended. However, whenever I try opening Activities(any entity for that matter), it is throwing an error dialog "An error has occured. For more information, contact you system administrator." I checked the event viewer, but there is no information about any error/exception. I searched for the CRM log, but couldn't locate it. Pls help me fix this issue. Thanks and Regards, Mahalax. ...

Transaction error in Money 2007
Since TAXCUT is out and Money is free with it (Staples), I purchased and upgraded from 06 to 07. My first download resulted in a transaction on my Fidelity account that listed the sale of one mutual fund and the purchase of another. Following the prompts I accepted each transaction but received the following error: An error occurred but Money was unable to retrieve and display details about what happened. (Resource = rcmpiad. Error = 4061.) I have tried deleting the transaction but it continually says 1 transaction to read. I have run salvage and it claimed to fix some on-line issue...

Access 97 conversion to 2000 and OpenRecordSet
We have a simple application developed in Acees 95 / upgraded to 97 and now we wish to move to 2000. the application 'almost' upgrades itself apart from a problem we are havinng with OpenRecordSet . The code is simple and I know (with hindsight) it can be much neater but when you do not spend a great deal of time and it works then leave alone. Code throws up Run Time eror 13 at the OpenRecordset command when run. Have read lots of docs but confused as to change this seems a major problem. Have checked DAO 3.6 to get round other problems and they seem to work. Help appreciated Pub...

ADAM Error with Web Service Installation
i need to install the Business Portal for GP 10.0 the web services is required. during the installation of the web services, i encountered an error " ADAM Error 128" what could cause this and how do i solve this? Any one help!!!!! Hi Taiwo Are the web services already installed? If they are it sounds like the issue may be that a different user account installed the web services (other then the one that is installing the BP) Fliehigh "Taiwo" wrote: > > i need to install the Business Portal for GP 10.0 > the web services is required. > during the insta...

C#, .net 3.5sp1 and Visual Studio 2008 sp1 errors list
What kind of errors will the errors list show when using C# 3.5sp1 with Visual Studio 2008sp1? "Andy B." <a_borka@sbcglobal.net> wrote in message news:%23tmIzu4vKHA.404@TK2MSFTNGP02.phx.gbl... > What kind of errors will the errors list show when using C# 3.5sp1 with > Visual Studio 2008sp1? e Why would you expect any? On the face of it vs 2008 sp1 will target .Net framework 3.5 sp1 by default. Andy B. wrote: > What kind of errors will the errors list show when using C# 3.5sp1 with > Visual Studio 2008sp1? That all depends on what ki...

error 0x8004210 occurs when I try to send email from outlook Vista
When I send emails addressed to any number of recipients from my att.net email account, I receive: Task 'email@ATT.net - Sending' reported error (0x80042109) : 'Outlook cannot connect to your outgoing (SMTP) e-mail server. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' I get the same message intermittently when I try to send emails to multiple recipients from my gmail accounts (I can send to individuals). "BMcD" <BMcD@discussions.microsoft.com> wrote in message news:D1DFD17C-6...

CRM Callout error (ComException) 02-03-04
I followed the example of Microsoft on how to create a Callout COM interface (found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crmPostCalloutsv1d2.asp). But I keep getting the same error. System.Runtime.InteropSevices.ComException: The file name is too long. I used very short names so I don't understand where this error would come from. Anybody got any idea's or encountered this in the past? Regards, RBakkers ...

conversions
I'd like to convert a unreadable text in Windows European Thanks for your help ...

Error 1406
Hi Everyone: Thus far, I am only a wannabe new user. I am not able to even get Outlook 2007 installed because every time I try, I receive the following message: Error 1406. Setup cannot write the value to the registry key \sketchObj.SketchInk. Verify that you have sufficient permissions to access the registry or contact Microsoft Product Support Services (PSS) for assistance. For information about how to contact PSS see C:\Users\Edward~1\AppData\Local\Temp\Setup 00000408\PSS10R,CHM and then the installation stops and has to be aborted before I can continue with anything...

0xc0000135 boot-up error
My hard drive crashed and a technician reinstalled. On XP boot-up, however, I get an error message like: "Cannot find file 0xc0000135. Click OK to terminate" How do I get rid of this nonsense? You receive a "Stop: c0000135" and "winsrv was not found" error message after you install Windows XP Service Pack 2 <http://support.microsoft.com/kb/885523/en-us> -=- "Mike Row Soft" wrote: > My hard drive crashed and a technician reinstalled. On XP boot-up, however, > I get an error message like: > > "Cannot f...

Internal error 2107
Hello, I have a user that is receiving an internal error: #2107 every time he tries to print. He is using version 5 on an XP Pro box. What is this error and how does one get around it? Thanks for any thoughts... ...