locked
Removing decimal point in query output RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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