By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Error converting data type varchar to numeric

P: 7
Hi

I wonder if anyone can help - I think the answer is simple but it's been a really long day and I need to get this done!!

My select statement goes like this...


SELECT
client,
apar_id,
ext_inv_ref,
case when amount ='' then '0' else amount end,
currency,
convert (varchar,payment_date,112),
voucher_no,
convert (varchar,voucher_date,121)

from acuhistr
where client = 'SD'
and account = '9299'

I get the error: Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I get why it's saying it, but I cannot work out how to convert the 'blank' into numeric or the numeric into varchar! Any ideas muchly appreciated
Oct 1 '09 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
What's the data type of your amount column? What do you mean by this line:

Expand|Select|Wrap|Line Numbers
  1.  
  2. case when amount ='' then '0' else amount end
  3.  
  4.  

--- CK
Oct 1 '09 #2

P: 7
Hiya

The amount column is a numeric I believe. What I am saying in the case statement is if the amount is blank then populate the result with 0 or 0.00 or else put the result amount in.

I have just rerun the query, and I have actually got .000 in my result! I think my problem actually lies in getting it to print the 0.00 or.000 in my ARW that I am putting the sql into. This is my result so far:
SD 29963 888 299.000 DKK 20091001 3000000 2009-10-01 00:00:00.000
SD 29963 444 .000 DKK 20091001 3000002 2009-10-01 00:00:00.000

Thanks anyway, but I am going to have to look at my report that I am using the sql in rather than the sql itself....I think it's going to be another long day :)
Oct 2 '09 #3

ck9663
Expert 2.5K+
P: 2,878
By default, numeric data stores NULL, not blank. If what you're trying to do is to return 0 or 0.00 instead of NULL, use the ISNULL() function instead.

Happy Coding!!!

--- CK
Oct 2 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.