none
tSQL DROPPING NUMBER ON DECIMAL IN TABLE

    Question

  • I have a column in my table name feeamount.  I have numbers like 0.809, 0.734 and even 0.752.

    What tsql statement do I run to remove the their decimal place so my number will appear in the table as 0.80, 0.73 and 0.75?  In other works drop the third place.

    THANKS FOR YOU HELP

    ANTONIA MCLAURIN
    Thursday, August 18, 2011 1:31 PM

All replies

  • Hi, try please :

    select Round(col,2) from feeamount
    

     

     

    Best regards
    Thursday, August 18, 2011 1:33 PM
  • As Badii has stated you could alter it as part of the select statement or if you are needing to only store 2 decimal places in the actual table you will need to run an alter table statement.

     

    Alter Table [TableName]
    Alter Column [ColumnName] decimal(x,2) -- gives you 2 decimal places.

    It coumes down to your requirements of needing to either store 2 or 3 decimal points.

     

    Thanks


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    Thursday, August 18, 2011 1:38 PM
  • > 0.809 -> 0.80

    do you want to truncate or round?

    select convert(decimal(5, 2), round(c1, 2, 1)) as [truncate], convert(decimal(5, 2), c1) as [round]
    from (values(0.809), (0.734), (0.752)) as T(c1);

     


    AMB

    Some guidelines for posting questions...

    Thursday, August 18, 2011 1:41 PM
    Moderator
  • Thank you!  But I don't want to round the numbers because this is in the account system.  It will throw the ledger off.  Do I still use this command?


    ANTONIA MCLAURIN
    Thursday, August 18, 2011 1:44 PM
  • Thank you!  But I don't want to round the numbers because this is in the account system.  It will throw the ledger off.  Do I still use this command?


    ANTONIA MCLAURIN


     

    Then try :

    select FLOOR(col*100)/100 from feeamount
    --OR
    select ROUND(col,2,1) from feeamount
    

     

     

    Best regards
    Thursday, August 18, 2011 1:46 PM
  • if you dont want to make the ledger to go into in-balance then I suggest you to use the truncate function as suggested by the Hunchback.

    Then caliculate the difference of the presnet total and the previous total and post this to an write-off account saying it is the rounding difference. 

    I think that will get you ledgers balanced.

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Thursday, August 18, 2011 1:48 PM
  • Thank you actually..I want to truncate all amounts in the current_fee_amount column in the procedure_fee table  ...so in other words all number like will three decimal places I want them two dec. places

     

    eg. .784 to .78


    ANTONIA MCLAURIN
    Thursday, August 18, 2011 1:52 PM
  • Thank you!  But I don't want to round the numbers because this is in the account system.  It will throw the ledger off.  Do I still use this command?


    ANTONIA MCLAURIN


     

    Then try :

    select FLOOR(col*100)/100 from feeamount
    
    --OR
    
    select ROUND(col,2,1) from feeamount
    
    

     

     

    Best regards


    Badii,

    Are you testing your suggestions?

    select floor(0.809 * 100) / 100 as c1, round(0.809, 2, 1) as c2;
    GO

    Result:

    c1           c2
    0.800000 0.800

    The OP is looking for 0.80, so the result somehow need to be converted or casted to use lower scale.

     


    AMB

    Some guidelines for posting questions...

    Thursday, August 18, 2011 1:52 PM
    Moderator
  • SO

    Alter Table Procedure_fees

    Alter Column Current_Fee_Amount Decimal (5,2)?

     

    I want two decimals


    ANTONIA MCLAURIN
    Thursday, August 18, 2011 1:53 PM
  • I wan to keep 5 number before the decimal.
    ANTONIA MCLAURIN
    Thursday, August 18, 2011 1:53 PM
  • Thank you!  But I don't want to round the numbers because this is in the account system.  It will throw the ledger off.  Do I still use this command?


    ANTONIA MCLAURIN


    If you want to see this as the result of a query, then round and convert. If you need to alter the table, then first update the values using round (function = 1) and then alter the column to use a new scale.

    USE tempdb;
    GO
    CREATE TABLE dbo.T (c1 decimal(8, 3));
    
    INSERT INTO dbo.T(c1)
    VALUES (0.809), (0.734), (0.752);
    
    SELECT CONVERT(decimal(8, 2), ROUND(c1, 2, 1)) as new_c1
    FROM dbo.T;
    GO
    UPDATE dbo.T
    SET c1 = CONVERT(decimal(8, 2), ROUND(c1, 2, 1));
    GO
    ALTER TABLE dbo.T
    ALTER COLUMN c1 decimal(8, 2);
    GO
    SELECT * FROM dbo.T;
    GO
    DROP TABLE dbo.T;
    GO
    

    Try to do the change when no users are working with this table, if possible.

     


    AMB

    Some guidelines for posting questions...

    Thursday, August 18, 2011 1:59 PM
    Moderator
  • @Hunchback, with convertion :

    select CAST(floor(0.809 * 100) / 100 AS numeric(5,2)) as c1, CAST(round(0.809, 2, 1) as numeric(5,2)) as c2;
    GO
    --0.80 0.80
    

     

     

    Best regards
    Thursday, August 18, 2011 2:01 PM
  • Hey would

     

    Alter Table Procedure_fees

    Alter Column Current_Fee_Amount Decimal (5,2) work?

     

    I am trying to do this with simplicity.  I need to convert the current_fee_amount column not just three numbers.


    ANTONIA MCLAURIN
    Thursday, August 18, 2011 2:19 PM
  • Hey would

     

    Alter Table Procedure_fees

    Alter Column Current_Fee_Amount Decimal (5,2) work?

     

    I am trying to do this with simplicity.  I need to convert the current_fee_amount column not just three numbers.


    ANTONIA MCLAURIN


    No, without updating the values before altering the column, it will be similar to just rounding (not truncating).

    USE tempdb;
    GO
    CREATE TABLE dbo.T (c1 decimal(8, 3));
    
    INSERT INTO dbo.T(c1)
    VALUES (0.809), (0.734), (0.752);
    GO
    --UPDATE dbo.T
    --SET c1 = CONVERT(decimal(8, 2), ROUND(c1, 2, 1));
    GO
    ALTER TABLE dbo.T
    ALTER COLUMN c1 decimal(8, 2);
    GO
    SELECT * FROM dbo.T;
    GO
    DROP TABLE dbo.T;
    GO
    
    /*
    
    0.81
    0.73
    0.75
    
    */
    


    Notice that 0.809 was rounded to 0.81 and not truncated.

     

     


    AMB

    Some guidelines for posting questions...

    Thursday, August 18, 2011 2:37 PM
    Moderator
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Wednesday, August 24, 2011 5:44 AM
    Moderator
  • Please try this -

    SELECT CAST(ROUND(12345.809,2,1) AS DECIMAL(7,2))
    



    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Wednesday, August 24, 2011 7:55 AM