SQL Server CAST Weirdness

Consider the following number:

3.04878048780488E-03

That's the number that was stored within a varchar field in my client's database. A stored procedure was trying to take the sum of this column and store the result into a decimal(18,2) column of a different table. The author of the stored procedure was smart enough to check for numeric values before blindly trying to take the sum, as in the following:

sum(case when isnumeric(theField) = 1 then cast(theField as decimal(18,2)) else 0 end) as theFieldSum

If you think this is the right way to have accomplish the task, then I'll admit that I was the author. Otherwise, we don't know who wrote this. ;-)

Anyways, the number above is considered numeric by SQL Server, and in fact, you can cast it to a float without problems. However, try casting to a decimal(18,2), and you get:

Msg 8114, Level 16, State 5, Line 11
Error converting data type varchar to numeric.


The exact behavior is documented in the BOL:

Character expressions that are being converted to an approximate numeric data type can include optional exponential notation (a lowercase e or uppercase E followed by an optional plus (+) or minus (-) sign and then a number).

Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string.