Rounding Down in Teradata

Database

Rounding Down in Teradata

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
Enthusiast

Re: Rounding Down in Teradata

can u try this

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

Re: Rounding Down in Teradata

Hello,

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

Regards,

Adeel

Enthusiast

Re: Rounding Down in Teradata

Hello,

If possible please provide the query.
Teradata Employee

Re: Rounding Down in Teradata

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,

Adeel

Enthusiast

Re: Rounding Down in Teradata

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,
CAST((col1-0.5) AS INTEGER) as answer
FROM Table1;
Enthusiast

Re: Rounding Down in Teradata

U can also use WIDTH_BUCKET function to achieve the same.

Enthusiast

Re: Rounding Down in Teradata

Hi Adeel,

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.

please help me in this regard.
Enthusiast

Re: Rounding Down in Teradata

Hi Venkatesh,

As per your requirement, you can use CASE statement to achieve the same.
Extract last 2 digits by SUBSTR function and if it is >=50 then subtract the same from the number else cast the number to the next digit.

Thanks.