Post reply

Scientific Number Format

  • September 12, 2003 at 10:24 am

    #159910

    I run the MDX query from a SQL stored procedure. The results that I get sometimes return in the scientific format like this: 8.5652588276452779E-2 or 8.2360569935143943E-3

    If I pass the results directly to the ASP page or Crystal reports that's how they are displayed.

    I need to display these numbers in a 2 decimal format: 0.09 and 0.01

    Right now we implemented a DLL to format the numbers.

    Is there a way to pass them to the report directly? There has to be an easier way.

  • September 12, 2003 at 1:58 pm

    #473986

    Inside the SQL code you could CONVERT( NUMERIC( 6,2), value ) as Name

    Or both ASP and Crystal Reports have the ability format the output (although I imagine you are looking for the results before the display environment)

    Guarddata-

  • September 12, 2003 at 4:21 pm

    #474009

    My guess is that the results coming from SQL Server are not actually in the scientic notation format you see but that either Crystal or ASP is doing the conversion on you. I would think that if you used the formatting function from within Crystal and ASP you can then format the results correctly for all data.

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • September 17, 2003 at 1:12 pm

    #474723

    Thanks for your replies, guys.

    What happens is the data comes back from the cube in the nvarchar format which I cannot immediately convert to decimal or numeric. I'm forced to use a temp table and convert into varchar(100) first. Meanwhile, the results still show like 8.2360569935143943E-3

    When I try to convert this column to decimal, I get the following error message on the first row that has the number in the scientific format:

    Server: Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

    Moreover, when I try to pass these numbers as nvarchars to Crystal and use CDble() function, it produces an error. I need to format it before I pass to Crystal or ASP.

  • September 17, 2003 at 1:21 pm

    #474730

    You could try something like

    SELECT CONVERT( DECIMAL( 12,2), CONVERT( FLOAT, '8.2360569935143943E-3' ))

    Guarddata-

  • September 17, 2003 at 1:34 pm

    #474734

    This seems to work.

    Thanks Guarddata!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply