How do I verify/validate a float before storing as decimal(4,1) in SQL?

Question

I have defined a column in SQL to be decimal(4,1), null which means I am storing four digits, up to one of which can be to the right of the decimal point.

The source data should always be in the range of 0 to 999.9, but due to an error beyond my control, I received the number -38591844.0. Obviously this won't store in SQL and gives the error "Arithmetic overflow error converting real to data type numeric."

I want to store null if the value is out of bounds.

What is the best way to verify that the value, a float in C#, is not out of bounds for a SQL column defined as above?

While this seems like the simplest approach...

private bool Validate41Float(float f)
{
    if (f >= 0 && f <= 999.9) return true;
    else return false;
}

I'm concerned if for some reason I get the value: 10.12345. In such a case, I would just want to store 10.1, but does the extra precision cause the same SQL error?

Answer 1

--- Edited after comment which clarified question ---

Most databases will round the number to fit the format. I'm not sure if that is a SQL standard or just a common practice amongst database vendors.

If the number is too large to store in the database (like 123456.7) most databases will raise an error that the number can't fit into the precision. of the field.

--- Original post follows ---

Floats don't represent exact data values, they just represent data values that can properly be represented as the sum of fractions of base 2.

For example 1.75 will exactly be representable, because it is 1 + 1/2 + 1/4. A number like 1 and 1/3 cannot be exactly represented because no sum of fractions in the form of 1/2^n can ever represent 1/3.

With that in mind, if this is really a critical issue, you need to do one of the following:

1. Round (or trim) the result to the closest thing to .1, .2, .3, etc. This will still have issues, but it is better than nothing.
2. Represent the number as an integer, and just "remember" that the integer is the number of tenths, not the number of ones.

Banking applications often use the second technique, to remove the possibility of fractions of a penny and rounding errors.

Answer 2

I know you can submit 10.12345 to SQL Server and it will just round to 10.1 and not complain. I don't know about other DB vendors for sure, but I would assume that they work the same.

You can have negative values, as well. To be clear what you're doing, you might want to explicitly round and then test your logic like this:

private bool Validate41Float(float f)
{
    float rounded = (float)Math.Round(f, 1);
    if (rounded > -1000 && rounded < 1000) return true;
    else return false;
}

Answer 3

Here's the extension method I implemented, based on Tim Coker's answer:

    private static float? ValidateDecimal41(this float? f)
    {
        if (!f.HasValue) return f;
        float rounded = (float)Math.Round((float)f, 1);
        if (rounded >= 0 && rounded <= 999.9) return rounded;
        else return null;
    }

This allowed me to:

• continue to accept and store null values,
• convert to null any value that was deemed out of bounds for the value in question, and
• round to one decimal point to avoid potential precision errors in SQL