How to select only two decimal number without rounding in sql?-CodePudding

My problem is I want to select two decimal number in amount column without rounding up. I have tried use FORMAT but rounding up my values

sql:

SELECT claim_type,amount
FROM detail

table detail :

id       claim_type             amount   
------   --------------------   -------              
1        FOOD REFRESHMENT       1.27000             
2        FOOD REFRESHMENT       2.35000

I want the output be like this when I select

claim_type             amount   
--------------------   -------              
FOOD REFRESHMENT       1.27            
FOOD REFRESHMENT       2.35

CodePudding user response：

This is dependent on the dialect of SQL, but in general your examples won't be rounded by either FORMAT or TRUNCATE analogues. If you have a number such as 2.237 and try to format it to two numbers after the decimal point, it will be rounded up to 2.24 by those functions - but you can always cast it to a string datatype and then simply take a substring. In PostgreSQL:

% select substring(2.237::text, 1, 4);

 substring 
-----------
 2.23
(1 row)

CodePudding user response：

you can also use the Cast function to get decimals values without rounding as below

SELECT Cast(Round(123.419,2,1) as decimal(18,2))