Post reply

Precision/Scale question

  • July 12, 2010 at 12:51 pm

    #141861

    I have data type of decimal (18,2)

    When I import this value from a txt file (124757266)

    In my table it comes in as 124757266.00

    I want it to look like

    1247572.66

    I thought with a precision of 2 it would do this, but it looks like it's adding zeros??

  • July 12, 2010 at 2:12 pm

    #1191683

    That's because 124757266 is a whole number. If your number was 1247572.66156413, then you'd get 1247572.66.

    If you want to add a decimal point, divide it by 100.

    INSERT INTO YourTable

    SELECT 124757266 / 100.00

    .......

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 12, 2010 at 2:26 pm

    #1191695

    so there's no way to move the decimal via precision/scale?

  • July 12, 2010 at 2:31 pm

    #1191701

    Nope, it will round the incoming data to fulfill the datatype, but not change the #.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 12, 2010 at 2:55 pm

    #1191713

    how would i update this column?

    update upb set upb = /100

  • July 12, 2010 at 3:00 pm

    #1191716

    --==== Build test table

    DECLARE @Table TABLE (

    RowID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    DecimalValue decimal(18,2) NOT NULL

    )

    --==== Insert test data into table

    INSERT INTO @Table(DecimalValue)

    SELECT 2316842 UNION ALL

    SELECT 594649871 UNION ALL

    SELECT 7424846 UNION ALL

    SELECT 987654184 UNION ALL

    SELECT 844365

    --==== Display Values pre-update

    SELECT * FROM @Table

    --==== Update Decimal Values to move decimal 2 places to the left

    UPDATE @Table

    SETDecimalValue = DecimalValue / 100

    --==== Display Values post-update

    SELECT * FROM @Table

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • July 12, 2010 at 3:14 pm

    #1191718

    Thanks MAN!

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

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