Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

SQL Convert error

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
W.E.B
Asked:
W.E.B
  • 4
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
W.E.BAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
W.E.BAuthor Commented:
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
 
W.E.BAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
W.E.BAuthor Commented:
Thanks,
SELECT (CAST(TransactionNumber as varchar(100))  + '-' + CONVERT (varchar, TransactionDate, 1)) AS [TransactionNumber]

worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now