Solved

SQL Convert error

Posted on 2013-11-17
7
406 Views
Last Modified: 2013-11-17
Hello,
Can you please help,
I'm getting an error when I use below code

Conversion failed when converting the varchar value ' xxxxxx' to data type int.

note:
TransactionDate is a DATETIME


SELECT (TransactionNumber + '-' + CONVERT (varchar, TransactionDate, 1)) AS [TransactionNumber]
FROM AROpentransactions
WHERE transactionType = 1 AND accountNumber = @accountNumber AND (cancelled = 0 OR cancelled IS NULL)
ORDER BY transactionNumber DESC

Any help is appreciated.
thanks,
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39654599
Some number that is non-numeric is being converted to an int that can't be.

For example, 'banana', 'BR549', and '1-234' cannot be converted to an integer.

So looking at the above...
What data type is @accountNumber and accountNumber?
What data type is cancelled?
What data type is transactionType?

Based on the error message you received, one of these values has an ' xxxxx' in it.
0
 

Author Comment

by:W.E.B
ID: 39654611
Hello,
AccountNumber    int
cancelled                bit

the error I get xxxxx  
xxxxxxxxxx   is a  date


I just put xxxxxxxxxx in the error because of different dates for different clients.

Example error

Conversion failed when converting the varchar value ' 2013/05/18' to data type int.

thanks,
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39654631
Ok.  I'd run the below T-SQL to flush out non-int values, and if there is more than one int column involved with a conversion run it for them as well, changing the column name:  
SELECT AccountNumber
FROM YourTable
WHERE ISNUMERIC(AccountNumber) = 0

Open in new window

Then when you find any values, ask yourself (1) do I want to UPDATE them right away to a correct int value, or (2) do I want to leave the values alone, and exclude them from the SQL in your question?

If (1), handle that on your own with UPDATE statements to change the values to whatever is appropriate.

If (2), add a WHERE ISNUMERIC(AccountNumber) = 1 to the end of the query, which filters it for only numeric values.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:W.E.B
ID: 39654676
Appreciate the help,
I still get the same error

SELECT (TransactionNumber + '-' + CONVERT (varchar, TransactionDate, 111)) AS [TransactionNumber]
FROM AROpentransactions
WHERE transactionType = 1 AND accountNumber = 1645 AND (cancelled = 0 OR cancelled IS NULL)
and  ISNUMERIC(AccountNumber) = 1
ORDER BY transactionNumber DESC

thanks,
0
 

Author Comment

by:W.E.B
ID: 39654712
Hello,
just to clarify,

if I change 111 to 112,

I get error
Conversion failed when converting the varchar value '_' to data type int.

All I'm trying to do is combine the TransactionNumber to the TransactionDate with a '-' or '_' in between.

Example
1645-2013/11/15
or
1645-2013.11.15

or anything similar.

thanks
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 300 total points
ID: 39654725
SELECT (TransactionNumber + '-' + CONVERT (varchar, TransactionDate, 1)) AS [TransactionNumber]

Open in new window

What is the data type of TransactionNumber?  If you're intent is to concatenate it with a varchar, then it also has to be a varchar.  (i.e. apples to apples, oranges to oranges).  So if it's currently not a varchar, you'll need to do this...

SELECT CAST(TransactionNumber as varchar(100))  + '-' + CONVERT (varchar, TransactionDate, 1)) AS [TransactionNumber]

Open in new window

0
 

Author Closing Comment

by:W.E.B
ID: 39654739
Thanks,
SELECT (CAST(TransactionNumber as varchar(100))  + '-' + CONVERT (varchar, TransactionDate, 1)) AS [TransactionNumber]

worked.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question