Truncate not round decimal places in SQL Server

Question

I m trying to determine the best way to truncate or drop extra decimal places in SQL without rounding   For example   declare  value decimal 18 2   set  value   123 456   This will automatically round  value to be 123 46  which is good in most cases   However  for this project  I don t need that   Is there a simple way to truncate the decimals I don t need   I know I can use the left   function and convert back to a decimal   Are there any other ways

User · Answer

I know this question is really old but nobody used sub-strings to round   This as advantage the ability to round really long numbers  limit of your string in SQL server which is usually 8000 characters    SUBSTRING  123 456   1  CHARINDEX       123 456     2

User · Answer

ROUND number  decimals  operation   number   gt  Required  The number to be rounded decimals   gt  Required  The number of decimal places to round number to operation   gt  Optional  If 0  it rounds the result to the number of decimal  If another value than 0  it truncates the result to the number of decimals  Default value is 0 SELECT ROUND 235 415  2  1   will give you 235 410 SELECT ROUND 235 415  0  1   will give you 235 000 But now trimming0 you can use cast SELECT CAST ROUND 235 415  0  1  AS INT   will give you 235

User · Answer

SELECT CAST Value as Decimal 10 2   FROM TABLE NAME    Would give you 2 values after the decimal point   MS SQL SERVER

User · Answer

SELECT Cast Round 123 456 2 1  as decimal 18 2

User · Answer

Another way is ODBC TRUNCATE function   DECLARE  value DECIMAL 18 3   123 456   SELECT  value AS val   fn TRUNCATE  value  2   AS result   LiveDemo  Output    -------------------       val      result       --------- ---------      123 456    123 450     -------------------    Remark   I recommend using built-in ROUND function with 3rd parameter set to 1

User · Answer

ROUND   123 456   2   1     When the third parameter    0 it truncates rather than rounds  http   msdn microsoft com en-us library ms175003 SQL 90  aspx  Syntax  ROUND   numeric expression   length    function       Arguments   numeric expression Is an expression of the exact numeric or approximate numeric data type category  except for the bit data type  length Is the precision to which numeric expression is to be rounded  length must be an expression of type tinyint  smallint  or int  When length is a positive number  numeric expression is rounded to the number of decimal positions specified by length  When length is a negative number  numeric expression is rounded on the left side of the decimal point  as specified by length  function Is the type of operation to perform  function must be tinyint  smallint  or int  When function is omitted or has a value of 0  default   numeric expression is rounded  When a value other than 0 is specified  numeric expression is truncated

User · Answer

I think we can go much easier with simpler example solution found in Hackerrank      Problem statement  Query the greatest value of the Northern Latitudes    LAT N  from STATION that is less than 137 2345  Truncate your answer   to 4 decimal places    SELECT TRUNCATE MAX LAT N  4  FROM STATION WHERE LAT N  lt  137 23453    Solution Above gives you idea how to simply make value limited to 4 decimal points  If you want to lower or upper the numbers after decimal  just change 4 to whatever you want

User · Answer

I think you want only the decimal value  in this case you can use the following   declare  val decimal  8  3  SET  val   123 456  SELECT  val - ROUND  val 0 1

User · Answer

select convert int  value

User · Answer

Round has an optional parameter  Select round 123 456  2  1   will   123 45 Select round 123 456  2  0   will   123 46

User · Answer

Try like this  SELECT cast round 123 456 2 1  as decimal 18 2

User · Answer

select round 123 456  2  1

User · Answer

I know this is pretty late but I don t see it as an answer and have been using this trick for years   Simply subtract  005 from your value and use Round  num 2    Your example   declare  num decimal 9 5    123 456  select round  num- 005 2    returns 123 45  It will automatically adjust the rounding to the correct value you are looking for   By the way  are you recreating the program from the movie Office Space

User · Answer

Please try to use this code for converting 3 decimal values  after a point into 2 decimal places   declare  val decimal  8  2  select  val   123 456 select  val     val  select  val   The output is 123 46

User · Answer

Mod x 1  is the easiest way I think

User · Answer

Do you want the decimal or not   If not  use   select ceiling  value  floor  value    If you do it with 0 then do a round   select round  value 2

User · Answer

Here s the way I was able to truncate and not round     select 100 0019- 100 0019  001    returns 100 0010  And your example     select 123 456- 123 456  001    returns 123 450  Now if you want to get rid of the ending zero  simply cast it   select cast  123 456- 123 456  001   as decimal  18 2     returns 123 45

User · Answer

Another truncate with no rounding solution and example       Convert 71 950005666 to a single decimal place number  71 9      1  71 950005666   10 0   719 50005666     2  Floor 719 50005666    719 0     3  719 0   10 0   71 9      select Floor 71 950005666   10 0    10 0

User · Answer

Actually whatever the third parameter is  0 or 1 or 2  it will not round your value   CAST ROUND 10 0055 2 0  AS NUMERIC 10 2

User · Answer

This will remove the decimal part of any number  SELECT ROUND  val 0 1

[sql] Truncate (not round) decimal places in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to rounding