error converting data type varchar to float. in sql server 2008

Question

0.00/5 (No votes)

See more:

Hello ,
i have a table test in this a column name 'observation_value_numerical' datatype is varchar(10). I want to write query

SQL

SELECT top 4 CONVERT(float, observation_value_numerical),convert(float,'9.0'),* FROM LAB_OBSERVATION where cast (observation_value_numerical as float)>9

But it showing error converting data type varchar to float. in sql server 2008

Table structure is
observation_value_numerical
11.4
5.7
9.2
7.7

how to resolve this error

thanks
Srishti

Posted 24-Aug-14 23:52pm

srishti_

Add a Solution

Comments

ChauhanAjay 25-Aug-14 5:55am

just try the below query
SELECT top 4 cast(observation_value_numerical as float),cast('9.0' as float),* FROM LAB_OBSERVATION where cast(observation_value_numerical as float)>9.0

3 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)

OriginalGriff · Answer 1 · 2014-08-25T00:02:00

Solution 1

The most obvious is: don't store it as a string at all.
Somewhere in your table there is a value which contains something other that digits and a decimal point, or the locale for the server is set to a culture that doesn't use '.' as a decimal separator.

Change the table so that you store the data as float values: then all you unpleasant-to-read code disappears, and the problem does as well.

Never store items as strings unless they are strings: store dates as DateTime values, store numbers as Integers, or floats. It may seem like a little more work, but it saves a lot of hassle an trouble later - as you are starting to see here.

Posted 25-Aug-14 0:02am

OriginalGriff

Comments

srishti_ 25-Aug-14 6:07am

i cant change my table structure thanks

OriginalGriff 25-Aug-14 6:16am

Why not?
If you don't, then you can expect this kind of problem to crop up again, and again, and again - and it will be your app that is blamed, not the DB design, or the person who input the (wrong) data.

ChauhanAjay 25-Aug-14 6:47am

You can create a backup of the old table and then create a new structure and get the data from the backup table.

OriginalGriff 25-Aug-14 6:57am

You don't really need to: if he fixes the invalid entries, SQL will let him just change the column type to float, and will convert all the values for him.

Peter Leow · Answer 2 · 2014-08-25T00:11:00

Solution 3

Try this:

SQL

SELECT top 4 CONVERT(float, observation_value_numerical),
convert(float,'9.0'),* FROM LAB_OBSERVATION
where CONVERT(float, observation_value_numerical)> 9

Posted 25-Aug-14 0:11am

Peter Leow

Updated 25-Aug-14 0:13am

v3

vikrant vaibhav · Answer 3 · 2014-08-25T01:36:00

Solution 4

Use

SQL

CASE WHEN ISNUMERIC(observation_value_numerical) THEN CONVERT(float, observation_value_numerical)

to convert varchar to float.

Posted 25-Aug-14 1:36am

vikrant vaibhav

Updated 31-Mar-15 3:52am

v2