How to get the integer part of a numeric field

Database
Enthusiast

How to get the integer part of a numeric field

I'm trying to get the integer part of the number in a field that contains numerics. Unfortunately the first try with FLOOR() and CEILING() functions failed (seems not supported). The second attempt I tried is to convert the number into string, however I always get the string back in the format of scientific notation (i.e. 1.20E2, instead of 120.00).

I tried to use CONVERT function (CONVERT ** AS SQL_BIGINT), which return correct results. However I'm still curious about how I can make it with some functions similar to FLOOR()/CEILING(), and how I can convert the number into string. Thanks in advance.
2 REPLIES
Junior Contributor

Re: How to get the integer part of a numeric field

It's CAST(xx as BIGINT).

There's no CONVERT in SQL/Teradata, if it worked you probably used SQL Assistant and tools -> options -> query -> "allow use of sql extensions in queries" was checked. This enabled some stupid ODBC SLQ syntax, which should be avoided because that query will fail when submitted using other tools/BTEQ/JDBC etc.

FLOOR/CEILING is missing in Teradata, afaik TD13.10 will include them.

For casting to a a string you need to add a format without fractional part. And because there's no format in Standard SQL you might use old style Teradata syntax for it:

select -123456789.123456778 (format '-(9)9') (varchar(11))

You'll find details about formats in SQL Data Types and Literals manual

Dieter
Enthusiast

Re: How to get the integer part of a numeric field

Hi Dieter,

Thank you very much for the reply. The information is really helpful. I'm using escape sequences like {fn CONVERT(%1, SQL_BIGINT)} in my code that access Teradata database via ODBC drivers. I'll change to use CAST as you adviced.

Eliot