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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!