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 220.127.116.11.
Teradata Studio V15.10.11
Any ideas how to get the correct value ?
Thanks in advance
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 18.104.22.168 and get the same answer as you.
I'd open a TaYS incident and run it past the support folks.
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 22.214.171.124605311155 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.