All Forums Database
Venkatesh G 17 posts Joined 05/09
08 Jun 2009
Rounding down 2.50 to 2

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. Thanks & Regrds,Venkatesh G

lathavim 7 posts Joined 05/09
08 Jun 2009

See if this works.select CAST((value + 0.49) AS INTEGER) new_value

robpaller 159 posts Joined 05/09
11 Jun 2009

Check the SQL Reference manual in the Teradata Manuals. Chapter 3 explains Rounding and the impact of the RoundHafwayMagUp DBSControl parameter. This may be impacting the behavior you are seeing.Your other option is to use a CASE statement and colA MOD 1 to force the issue:SELECT CASE WHEN colA MOD 1 <> 0.50 THEN CAST(colA AS DECIMAL(10)) ELSE CAST(colA AS INTEGER) ENDFROM TableA;

You must sign in to leave a comment.