how to convert varchar to float /double in sql?

Question

1.00/5 (2 votes)

See more:

hi, how can i convert varchar to int/float in sql??

SQL

select id,
  sum (case when [Month] = 1 then aa else 0 end) January,
 sum(case when [Month] = 2 then aa else 0 end) February,
   sum(case when [Month] = 3 then aa  else 0 end) March ,
  sum(case when [Month] = 4 then aa else 0 end) April ,
sum(case when [Month] = 5 then aa  else 0 end) May ,
  sum(case when [Month] = 6 then aa else 0 end) June ,
  sum(case when [Month] = 7 then aa  else 0 end) July ,
 sum(case when [Month] = 8 then aa  else 0 end) August ,
  sum(case when [Month] = 9 then aa  else 0 end) September ,
 sum(case when [Month] = 10 then aa  else 0 end) October ,
 sum(case when [Month] = 11 then aa  else 0 end) November ,
 sum(case when [Month] = 12 then aa  else 0 end) December
from order
group by id

when i execute the code above, i got error 'Conversion failed when converting the varchar value '310.2' to data type int.'
how can i solve this problem? please help me.
thanks in advanced :)

Posted 31-May-14 23:43pm

hahaahahaahahhahahahhahaha

Add a Solution

Comments

Sergey Alexandrovich Kryukov 1-Jun-14 14:50pm

Why using varchar in first place where you needed numeric types?
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2014-05-31T23:53:00

Solution 1

You see the case of SQLs auto-convert. On the same command you have aa and 0, so SQL try to bring them to the same type. The final type is decided by the last element which is 0 in your case, an integer. However aa is not integer (and somewhere has a value of 310.2) so SQL fails to convert. Change 0 to 0.0 to solve your problem...

SQL

sum (case when [Month] = 1 then aa else 0.0 end) January,

Posted 31-May-14 23:53pm

Kornfeld Eliyahu Peter

Comments

hahaahahaahahhahahahhahaha 1-Jun-14 6:16am

heii when i tried ur solution, i get another error 'Arithmetic overflow error converting varchar to data type numeric.'. actually i use varchar for my aa column due to some problem in my c#

Kornfeld Eliyahu Peter 1-Jun-14 6:34am

In that case your problem is with the value of aa - there are case when aa can not be converted to numeric at all! You have to normalize your DB in such way that your type fits the values and the values fit the type.
About the problem in your C# code that force you to change SQL column type - it sounds like a huge design problem, review it!!!

hahaahahaahahhahahahhahaha 2-Jun-14 0:38am

okkk thanks! is solved it by myself. i changed the datatype into float.

Kornfeld Eliyahu Peter 2-Jun-14 2:01am

Good! That's the way!

soumyajayaraj · Answer 2 · 2014-06-01T00:22:00

Solution 2

Since you have told that you need 'aa' as string in your application, why don't you send both values as string

sum (case when [Month] = 1 then aa else '0' end) January,

Posted 1-Jun-14 0:22am

soumyajayaraj