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

Posted on 2005-04-22
Last Modified: 2012-05-05

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,

Question by:rss2
    LVL 17

    Accepted Solution

    Do it like this
    select CAST(CAST(a1 AS float) AS numeric(19,2))

    Author Comment

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now