Unable to Cast Value as Float

unable to cast value as float

Regarding your after thought.

It seems that it would be nice is T-SQL had the following functions to
check to see if a string could be converted to a particular datatype.

SQL Server 2012 does introduce TRY_CONVERT for this need. So the following query would return NULL rather than an error.

SELECT TRY_CONVERT ( FLOAT, 'Fish')

There is no guarantee even with serial plans that the WHERE clause will happen before the SELECT is evaluated. As explained in this blog post from SQL Server 2005 onwards this is more likely to happen than in previous versions. The Behavior Changes to Database Engine Features in SQL Server 2005 specifically calls this out as follows.

SQL Server 2005 sometimes evaluates expressions in queries sooner than
when they are evaluated in SQL Server 2000. This behavior provides the
following important benefits:

• The ability to match indexes on computed columns to expressions in a query that are the same as the computed column expression.
• The prevention of redundant computation of expression results.

More discussion about this behaviour is in another good blog post by Craig Freedman Conversion and Arithmetic Errors.

On versions prior to 2012 and TRY_CONVERT you need to wrap the CAST AS FLOAT in a CASE statement. e.g.

  SELECT CASE WHEN ISNUMERIC(Col)=1 THEN CAST(Col AS FLOAT) END AS Col
  FROM Table
  WHERE ISNUMERIC(Col)=1

This still isn't absolutely guaranteed to prevent you getting errors as ISNUMERIC itself just checks that the value would cast to one of the numeric datatypes rather than specifically to float An example of an input that would fail is '.'

CASE is documented to mostly short circuit in books online (some exceptions are discussed here)

You can also find additional discussion/complaints about this in the connect item SQL Server should not raise illogical errors and a good explanation of a similar issue by SQLKiwi

C# Unable to cast object of type 'System.Double' to type 'System.Single'

When you add to the dictionary your double will be boxed, as the dictionary is mapping a string to an object.

When you unbox you must cast to the underlying type. In your case that underlying type is a double, so the cast to float will fail.

You can get around this by using Convert.ToSingle

netPlannedHours = Convert.ToSingle(d["key"])

This method will work out the underlying type and can do the conversion, at the expense of a performance hit when working out the type conversions.

SQL: Unable to CAST a query

Try this:

SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,3)) FROM RawData r

Cast object containing int to float results in InvalidCastException

The problem you're running into here is that the C# cast operator means different things in different situations.

Take this example you gave:

object num = 10;
float fnum = (float)num;

The C# compiler will think you are telling it this: "The variable num refers to a boxed float; please unbox it and return the boxed value."

You're getting an error because it's not a boxed float, it's a boxed int.

The problem is that C# uses identical-looking syntax for two totally unrelated operations: 'unbox' and 'numeric conversion'. Here's an example of where a cast means numeric conversion:

int num = 10;
float fnum = (float)num;

Almost exactly the same code, and yet this won't give you an error. And that's because the C# compiler treats this completely differently - this code means: "Please perform a numeric conversion, converting the integer stored in 'num' into a single-precision floating point value."

How do you know which of these two utterly unrelated operations it's going to choose? It's all about the source and destination types. If you're converting from 'object' to a value type, that will always be treated as an unbox. If you're converting from one numeric type to another, that will always be treated as a numeric conversion.

So how do you get the result you want? Well, you need to do both operations: you need to unbox the int and then you need to convert it to a float. So you actually need two casts:

object num = 10;
float fnum = (float) (int)num; 

Horrible huh?

The simplest way to do what you want here is to avoid casting entirely. Just do this:

float fnum = Convert.ToSingle(num); 

That will coerce the type to a single-precision float if it's possible to do so.

Unable to cast object of type 'System.Single' to type 'System.Double'.'

Either you change all you DBTypes from REAL to FLOAT or your C# Types from double to float.

DBType REAL is float in C#
DBType FLOAT is double in C#

---

Related Topics