CODEWITHSUNDEEP
sqlteradata
user3055262
user3055262

Reputation: 405

converting decimal to integer in teradata

sel cast(9.1 as integer)as inttt;

Result:
9

sel cast(9.9 as integer)as inttt;
Result:
9

I executed the above queries on Teradata, and both of the queries resulted in the floor value.

Is this a hard rule for casting from DEC to INT? That is, does it always return the floor value?

Upvotes: 1

Views: 32018

Answers (5)

Spon4ik
Spon4ik

Reputation: 73

try this

SELECT CAST([COLUMN] AS INT) + CASE WHEN [COLUMN] MOD 1 >= 0.5 THEN 1 ELSE 0 END from [table]

results:
9.1 9
9.2 9
9.3 9
9.4 9
9.5 10
9.6 10
9.7 10
9.8 10
9.9 10
10  10

Upvotes: 0

GP-by
GP-by

Reputation: 21

Following code can be used to round decimal number:

Select CAST(number + 0.5 AS integer) from sometable

Upvotes: 2

Evan Volgas
Evan Volgas

Reputation: 2911

Which version of TD are you using? If 14, just use the CEIL function?

If before that, maybe try 

CASE WHEN CAST(number AS DEC(9,0) < number THEN CAST(number AS
DEC(9,0) + 1 ELSE CAST(number AS DEC(9,0)

(you could obviously adjust that to your own rounding logic)

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

As juergen_d already said, this should be the default for any language.

When you want rounding istead of truncating, you do a CAST(9.9 AS DEC(9,0)) or in TD14 there's a ROUND function, too. But be aware, there are two algorithms for rounding, check if your system uses the one you want :-)

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Yes it is.

If you cast a decimal value into an integer the decimal places get lost. That is in all languages (I know of) the case.

Upvotes: 3

Related Questions