Post reply

How to update a number with a decimal point

  • October 21, 2005 at 9:06 pm

    #363984

    I need to learn how to insert a decimal point in a number without changing or adding numbers.

    For example:

    I have a column called "amounts" and the data I imported looks like:

    12345

    34566

    44400

     

    I need to change the data to insert a decimal point so the data looks like:

    $ 123.45

    $ 345.66

    $ 444.00

    Can someone give me a simple sql query to do this and maybe even insert the $ symbol.

    Thank you!!

  • October 22, 2005 at 6:38 am

    #599352

    Just present it as money on the application side and you're done... you might also want to use decimal(9,2) on the server to keep that data and divide those numbers by 100.0 so that you don't have anything extra to do in the application code.

  • October 22, 2005 at 6:51 am

    #599353

    Remi beat me... This is a presentation issue, hence my second select will give you the output you want, but that is not really a SQL Server job...   

    CREATE TABLE #Money( [Amount] varchar(10))

    INSERT INTO #Money VALUES( 12345)

    INSERT INTO #Money VALUES( 34566)

    INSERT INTO #Money VALUES( 44400)

    SELECT (CONVERT( money, [Amount]) / 100) FROM #Money

    SELECT '$ ' + CONVERT( varchar(10), (CONVERT( money, [Amount]) / 100)) FROM #Money

    DROP TABLE #Money

    I wasn't born stupid - I had to study.

  • October 22, 2005 at 9:41 am

    #599363

    Thank you, that worked!!

    Excellent Help - Thanks again.

  • October 31, 2005 at 10:23 pm

    #601465

    >This is a presentation issue, hence my second select will give you the output you want, but that is not really a SQL Server job

    It depends... if there's no presentation layer and the output is to a file because a 3rd party needs it that way, then SQL Server is perfect for the job...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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