locked
Removing decimal point in query output RRS feed

  • Question

  • I have this query and need it just return whole numbers instead of decimal...how do I change it to meet the requirement...

    RIGHT(' '+REPLACE(AP.APRLOS, '.',''),3) AS TOT_AUTH_LEN_STAY,

    Thanks

    Wednesday, January 14, 2015 6:44 PM

Answers

  • select CEILING(AP.APRLOS) AS TOT_AUTH_LEN_STAY from Tablename

    OR

    select FLOOR(AP.APRLOS) AS TOT_AUTH_LEN_STAY from Tablename

    --Prashanth


    Wednesday, January 14, 2015 7:13 PM
  • You can use FLOOR function. 

    http://msdn.microsoft.com/en-us/library/ms178531(v=sql.105).aspx

    DECLARE @myDecimalVal Decimal(16, 2) = 12.85
    SELECT FLOOR(@myDecimalVal)
    
    --output
    12



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.



    • Edited by Arbi Baghdanian Wednesday, January 14, 2015 6:49 PM Add Sample
    • Proposed as answer by Charlie Liao Wednesday, January 21, 2015 1:30 PM
    • Marked as answer by Charlie Liao Sunday, January 25, 2015 2:18 PM
    Wednesday, January 14, 2015 6:47 PM
  • Or you can convert decimal to int:

    DECLARE @myDecimalVal Decimal(16, 2) 
    SET @myDecimalVal = 12.85
    SELECT CONVERT(int, @myDecimalVal)


    A Fan of SSIS, SSRS and SSAS

    • Proposed as answer by Charlie Liao Wednesday, January 21, 2015 1:30 PM
    • Marked as answer by Charlie Liao Sunday, January 25, 2015 2:18 PM
    Wednesday, January 14, 2015 7:04 PM

All replies

  • You can use FLOOR function. 

    http://msdn.microsoft.com/en-us/library/ms178531(v=sql.105).aspx

    DECLARE @myDecimalVal Decimal(16, 2) = 12.85
    SELECT FLOOR(@myDecimalVal)
    
    --output
    12



    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.



    • Edited by Arbi Baghdanian Wednesday, January 14, 2015 6:49 PM Add Sample
    • Proposed as answer by Charlie Liao Wednesday, January 21, 2015 1:30 PM
    • Marked as answer by Charlie Liao Sunday, January 25, 2015 2:18 PM
    Wednesday, January 14, 2015 6:47 PM
  • Or you can convert decimal to int:

    DECLARE @myDecimalVal Decimal(16, 2) 
    SET @myDecimalVal = 12.85
    SELECT CONVERT(int, @myDecimalVal)


    A Fan of SSIS, SSRS and SSAS

    • Proposed as answer by Charlie Liao Wednesday, January 21, 2015 1:30 PM
    • Marked as answer by Charlie Liao Sunday, January 25, 2015 2:18 PM
    Wednesday, January 14, 2015 7:04 PM
  • how will in add that to the current query or should I just delete the original and use yours..
    Wednesday, January 14, 2015 7:07 PM
  • You can use ceiling or Floor function depending on your requirement

    SELECT CEILING(12.9273); --13
    SELECT FLOOR(12.9273); --12
    --Prashanth


    Wednesday, January 14, 2015 7:09 PM
  • select CEILING(AP.APRLOS) AS TOT_AUTH_LEN_STAY from Tablename

    OR

    select FLOOR(AP.APRLOS) AS TOT_AUTH_LEN_STAY from Tablename

    --Prashanth


    Wednesday, January 14, 2015 7:13 PM
  • I have this query and need it just return whole numbers instead of decimal...how do I change it to meet the requirement...

    RIGHT(' '+REPLACE(AP.APRLOS, '.',''),3) AS TOT_AUTH_LEN_STAY,

    Thanks

    Your original query seems only capturing Decimal part of your APRLOS field. But you are requesting you need whole number. Please clarify. Thus, from 12.95 do you need number 12 or 96?

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Wednesday, January 14, 2015 7:27 PM