TO_NUMBER returns wrong value


TO_NUMBER returns wrong value


I'm trying to convert a series of hex values (stored as strings) to numbers ( BIGINT) using the TO_NUMBER function . A number of the results giivng the incorrect number

SELECT TO_NUMBER( '03ea02653f6938ba','XXXXXXXXXXXXXXXX' ) returns 282040560542890190

According to my scientific calculator the value is 282040560542890170

SELECT TO_NUMBER( '03ea02653f693e63', 'XXXXXXXXXXXXXXXX' ) returns 282040560542891600. Yet the value appears to be 282040560542891619

We're running Teradata Version

Teradata Studio V15.10.11

Any ideas how to get the correct value ?

Thanks in advance


Tags (1)
Senior Apprentice

Re: TO_NUMBER returns wrong value

Hi Rob,

No answers, but:

I've tested your first number and get the same answer as you in Win7 calc. I also ran the first query on TD and get the same answer as you.

I'd open a TaYS incident and run it past the support folks.



Ward Analytics Ltd - information in motion
Junior Contributor

Re: TO_NUMBER returns wrong value

You should try your query using BTEQ or SQL Assistant to check if it's a client issue.


I just tested it against a TD15.10.02.06, Teradata Studio returns the correct result, SQL Assistant using the Teradata.Net provider, too.

SQLA using ODBC returns a wrong answer (282040560542890000) but only when Options -> Data Format -> Display BigInt and Decimal(16)+ values as strings is unchecked.