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

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.