Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

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
0
rss2
Asked:
rss2
1 Solution
 
mokuleCommented:
Do it like this
select CAST(CAST(a1 AS float) AS numeric(19,2))
0
 
rss2Author Commented:
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!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now