Database

0.96
1.56

I have two value above. I want to round .96 to 0.00 and 1.56 to 1.00. Does anybody know how to do that ?

Thanks
Rajiv
8 REPLIES 8

can u try this

select cast(cast(0.96 as integer) as decimal(5,2))

Hello,

Casting may not work for custom rounding .... how about a CASE statement?

Regards,

Hello,

If possible please provide the query.

CREATE VOLATILE TABLE Table1 (Col1 DECIMAL(10,5)) ON COMMIT PRESERVE ROWS;
INSERT Table1 (0.96);
INSERT Table1 (1.56);

SELECT
Col1,
CAST(col1 AS INTEGER) AS I
,Col1 - I AS D
,CASE WHEN (D < 0.99) THEN I END AS Answer
FROM Table1;

SELECT
Col1
,CAST(Col1 AS VARCHAR(20)) AS C
,CASE WHEN Col1 < 1 THEN '0' || SUBSTRING(C, 0,INDEX(C, '.')) ELSE SUBSTRING(C, 0,INDEX(C, '.')) END AS Answer
FROM Table1;

HTH!

Regards,

I like this way better...

CREATE VOLATILE TABLE Table1 (Col1 DECIMAL(10,5)) ON COMMIT PRESERVE ROWS;
INSERT Table1 (0.96);
INSERT Table1 (1.56);

SELECT
Col1,
FROM Table1;

U can also use WIDTH_BUCKET function to achieve the same.

I have an issue with rounding the decimal to nearest integer.

if value = 123.45, I need to round it to 123
if value = 123.54, I need to round it to 124
I have achieved this with the cast(123.54 as decimal(10,0)) new_value
the problem is with the values of this kind 123.50

as per the requirements 123.50 should be converted into 123,
but the formula cast(123.54 as decimal(10,0)) is converting it into 124.