Rounding down 2.50 to 2

Database
Enthusiast

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 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.

Thanks & Regrds,
Venkatesh G
2 REPLIES
Enthusiast

Re: Rounding down 2.50 to 2

See if this works.

select CAST((value + 0.49) AS INTEGER) new_value
Enthusiast

Re: Rounding down 2.50 to 2

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) END
FROM TableA;