TO_NUMBER returns wrong value

Database

TO_NUMBER returns wrong value

Hi,

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

Teradata Studio V15.10.11

Any ideas how to get the correct value ?

Thanks in advance

Rob

Tags (1)
2 REPLIES
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 15.10.1.1 and get the same answer as you.

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

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
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 15.11.0.201605311155 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.

 

Dieter