Link to home
Start Free TrialLog in
Avatar of rss2
rss2

asked on

Scientific notation results in "Error converting data type varchar to numeric"

Hi,

I have a table with extremely small numbers which are written in scientific notation. In the sql table, they are actually varchar(50), and I need to convert them to numbers, then round them to a precision of 2.

For example, I get this error when the following statement is run:
select cast(a1 as numeric(19,2)) from alphas

.. when it reaches a value that has an 'E' in it, like: 6.81651e-005

How do I convert the scientific notation into strings that the cast command will understand?

Thank you,

Rebecca
ASKER CERTIFIED SOLUTION
Avatar of mokule
mokule
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rss2
rss2

ASKER

Actually, I sort of have one more question (maybe I should pop this into a new question?)

I have a value in a1 that is '0.000892734'. Using the double cast you suggest turns it into 0.00. I have changed my mind and would actually like to keep the precision as is. So when I just cast it once it turns '0.000892734' into 8.927399999999995E-4.

How do I get it to look exactly the same, just in numeric format? (Without the repeating decimal?)

Thank you!