What is the best way to recognize and convert these "minimum" double values into C# Double.MinValue?

Question

I have a database table that needs to be converted into current form. This table has three columns that are of type Double (it's Pervasive.SQL, if anyone cares).

My problem is that this table has been around for a long time, and it's been acted upon by code going back some 15 years or better.

Historically, we have always used Double.MinValue (or whatever language equivalent at the time) to represent "blank" values provided by the user. The absence of a value, in other words, is actually stored as a value that we can recognize later and react to intelligently.

So, today my problem is that I need to loop through these records and insert them into a newly created table (this is the "conversion" I spoke of). However, I am not seeing consistent values in the tables I am converting. Here are the ones I know of for sure:

2.2250738585072014E-308
3.99285938963E-313
3.99099435427E-313
1.1125369292536007E-308
-5.389000690742776E279
2.104687961E-314

Now, I recognize that there are other ways that Double.MinValue might exist or at least be represented. Having done some google searches, I found that the first one is another representation of Double.MinValue (actually DBL_MIN referenced here: http://msdn.microsoft.com/en-us/library/6bs3y5ya(v=vs.100).aspx).

I don't want to get too long-winded, so I'll solicit questions if this is not enough information to help me. Suffice it to say, I need a reliable way of spotting all of the previous values of "minimum" and replace them with the C# Double.MinValue constant as I am looping these data rows.

If it proves to be dataRow["Value"] < someConstant, then so be it. But I'll let the math theorists help me out with that determination.

Thank you for the time.

EDIT:

Here's what I am doing with these values as I find them. It's part of a generic method that assembles values to be written to the database:

else if (c.DataType == typeof(System.Double))
{
    if (inRow[c] == DBNull.Value)
        retString += @"NULL";
    else
    {
        Double d;
        if (Double.TryParse(inRow[c].ToString(), out d))
        retString += d.ToStringFull();
    }
}

Until now, it simply accepted them. And that's bad because when the application finds them, they look like acceptable data, and not like Double.MinValue. Therefore, not seen as blanks. But that's what they are.

Answer 1

This is utter craziness. Let's look at some of those numbers in detail. These are all tiny numbers just barely larger than zero:

2.2250738585072014E-308

This is 1 / 2¹⁰²² -- it is a normal double. This is one of the two "special" numbers in your set; it is the smallest normal double that is larger than zero. The rest of the small doubles on your list are subnormal doubles.

1.1125369292536007E-308

This is 1 / 2¹⁰²³ -- it is a subnormal double. This is also a special number; it is half the smallest normal double larger than zero. (I originally said that it was the largest subnormal double but of course that is not true; see the comments.)

3.99285938963E-313

This isn't anything special. It's a subnormal double equal to a fraction where the numerator is 154145 and the denominator is a rather large power of two.

3.99099435427E-313

This isn't anything special either. This time the numerator is 154073.

2.104687961E-314

This isn't anything special either. The numerator is 2129967929 and the denominator is an even larger power of two.

All the numbers so far have been very close to zero and positive. This number is very far from zero and negative, and therefore stands out:

-5.389000690742776E279

But again it is nothing special; it is nowhere even close to the negative double with the largest absolute value, which is about -1.79E308, about a billion times larger.

This is a complete mess.

My advice is stop this madness immediately. It makes absolutely no sense to use values that are incredibly close to zero to represent "blank" values; values that are incredibly close to zero should be rounded to zero, not treated as blanks!

Double already has a representative for "blank" values, namely Double.NaN -- Not A Number; it is bizarre to use a valid value to represent an invalid value when the domain already includes a specific "invalid" value. (Remember that there are actually a large number of distinct NaN bit patterns; use IsNaN to determine if a double is a NaN.)

So my advice is:

• Examine individually every number in the database that is a subnormal or very small normal double. Some of those probably ought to be zero and ended up as tiny values due to rounding errors. Replace them with zero. The ones that ought to be blank, replace with database null (best practice) or double NaN (acceptable, but not as good as database null.)

• Write a program to find every number in the database that is impossibly large in absolute value and replace it with database null or double NaN.

• Update all clients so that they understand the convention you're using to represent blank values.