SQL Convert error

Posted on 2013-11-17
Last Modified: 2013-11-17
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.

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.
Question by:W.E.B
  • 4
  • 3
LVL 65

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.

Author Comment

ID: 39654611
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.

LVL 65

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

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


Author Comment

ID: 39654712
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.


or anything similar.

LVL 65

Accepted Solution

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


Author Closing Comment

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


Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Sum Decimal Columns 11 34
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 29
Help Required 2 33
SQL Help 27 45
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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