All Forums Database
rkhurana 4 posts Joined 05/09
03 Jun 2009
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 ? ThanksRajiv

harimon 22 posts Joined 05/09
03 Jun 2009

can u try thisselect cast(cast(0.96 as integer) as decimal(5,2))

Adeel Chaudhry 773 posts Joined 04/08
03 Jun 2009

Hello,Casting may not work for custom rounding .... how about a CASE statement?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

harimon 22 posts Joined 05/09
04 Jun 2009

Hello,If possible please provide the query.

Adeel Chaudhry 773 posts Joined 04/08
04 Jun 2009

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 AnswerFROM 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 AnswerFROM Table1;HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Jesse 18 posts Joined 04/09
04 Jun 2009

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 answerFROM Table1;

Sunar 59 posts Joined 02/08
08 Jun 2009

U can also use WIDTH_BUCKET function to achieve the same.

Venkatesh G 17 posts Joined 05/09
08 Jun 2009

Hi Adeel,I have an issue with rounding the decimal to nearest integer.if value = 123.45, I need to round it to 123if value = 123.54, I need to round it to 124I have achieved this with the cast(123.54 as decimal(10,0)) new_valuethe problem is with the values of this kind 123.50as 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.

Sunar 59 posts Joined 02/08
09 Jun 2009

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.

You must sign in to leave a comment.