Problem converting very small doubles to SQL floats

Question

I'm trying to store a C# double in MS SQL 2005 as a float. However, there seem to be a range of small numbers which are valid doubles but which aren't valid floats. For example, when I try to store the value 1e-320 I get the error "the supplied value is not a valid instance of type float".

This is consistent with the documentation for SQL floats, which have a smallest value of 2.23e-308 http://msdn.microsoft.com/en-us/library/ms173773.aspx

and with the documentation for C# doubles, which have a smallest value of 5.0e-324 http://msdn.microsoft.com/en-us/library/678hzkk9(VS.71).aspx

So my question is what is the best way of handling this - can I cast the double as something which is a valid as a SQL float?

Answer 1

You can either change data type to decimal, or add a second column to hold the number of decimal places that stored value should be shifted by to yield an original value. That is, storing 0.000456 using this scheme will result in storing 4.56 and 5, whereas 34567.89 can be represented as 3.456789 and -4 (minus indicates shift to the left).

The second option, however, may result in incremental loss of precision.

Answer 2

Options:

• Store it as string and rely on c# to validate/understand it
• Store the mantissa and exponent separately: 1 and -320 and reconstitute outside the database

What you can't do:

• Change to decimal will not work because you then have a maximum of 38 decimal places

Edit:

SQL Sever just does not understand this number: it can not be stored in SQL Server as a number no matter what client library or c# datatype or trickery is used.

Answer 3

You could use the SqlDouble type. This is from the System.Data.SqlTypes namespace, which

provides classes for native data types in SQL Server. These classes provide a safer, faster alternative to the data types provided by the .NET Framework common language runtime (CLR). Using the classes in this namespace helps prevent type conversion errors caused by loss of precision. Because other data types are converted to and from SqlTypes behind the scenes, explicitly creating and using objects within this namespace also yields faster code.

EDIT: Apparently SqlDouble has a range of -1.79E +308 through 1.79E +308, which is the same as a double on the positive exponent side of things, and doesn't take the negative exponent discrepancy into consideration. Looks like it's not going to ever be useful from a range checking perspective. As a side note, other types in that namespace, like SqlDateTime, look like they might actually useful for range checking, but not SqlDouble.

Answer 4

Fundamentally, trying to store a 64-bit value in a 32-bit value will result in a loss of precision, and the compiler is right to warn you about the issue. What you really have to ask is "do I really need all that precision?"

If you don't need the precision, then "float output = Convert.ToSingle(inputAsDouble);" will do the trick - it will just round off to the nearest representable single precision value.

If you do need the precision, but still need the value to fit in 32-bits, then you have to constrain the range somehow. For example if you know that your value is always going to be in the range -1e-319 to 1e-319, then you can use fixed point mathematics to convert between the stored 32-bit value and the double value you need to use for calculations. The double value thus returned won't be able to represent all possible numeric values in your range, but you will have 32-bit granularity inside that limited range, which is really quite a decent accuracy.

For example, you can make a helper class like:

struct FixedDouble
{
    int storage;

    public FixedDouble(double input)
    {
        storage = DoubleToStorage(input);
    }

    public double AsDouble
    {
        get
        {
            return StorageToDouble(storage);
        }
    }

    const double RANGE = 1e-319;

    public static int DoubleToStorage(double input)
    {
        Debug.Assert(input <= RANGE);
        Debug.Assert(input >= -RANGE);

        double rescaledValue = (input / RANGE) * int.MaxValue;

        return (int)rescaledValue;
    }

    public static double StorageToDouble(int input)
    {
        double rescaledValue = ((double)input / (double)int.MaxValue) * RANGE;

        return rescaledValue;
    }
}

This code probably won't work as is because I've just knocked it out quickly, but the idea is there - basically you sacrifice the full range that the double offers you, and instead choose a fixed granularity between two numbers, and the 32-bit value allows you to define a point on the number-line between those two numbers.

Answer 5

If you want to maintain the full value of the double, you'll probably need to use one of @gbn's solutions.

If you just prefer to use the range of the SQL float, and round to zero if it's outside of that range, it should be fairly trivial to create a helper method that rounds to zero if the value is outside of the range for SQL float.

private static SqlParameter CreateDoubleParameter(string parameterName, double value)
{
    const double SqlFloatEpsilon = 2.23e-308;
    SqlParameter parameter = new SqlParameter(parameterName, SqlDbType.Float);
    parameter.Value = value > -SqlFloatEpsilon && value < SqlFloatEpsilon ? 0d : value;
    return parameter;
}