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

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

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


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,

1 Solution
Do it like this
select CAST(CAST(a1 AS float) AS numeric(19,2))
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!

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