Error ORA-01858

[9654]

I am running a sql in toad

select (to_date('01/JAN/1970')+(1122568480/86400)) from dual

to get normal date when there is epoch date stored which is the number of seconds since 1,1,1970 andit runs fine there.

But when i try to run this same sql in crystal reports using native connection it gives

Error - 01858 non-numeric character was found where numeric was expected.

This same sql runs fine in crystal reports when i am using ODBC connection.

 

[SantaMufasa]

9654,

The problem could be as simple as the default date format for the erroring environment differs from the non-erroring environment.

It is always the best policy to define explicitly the data-format mask in any to_char or to_date function. Therefore, I recommend your changing your code to read:

select (to_date('01/JAN/1970'[b],'dd/MON/yyyy'[/b])+(1122568480/86400))  from dual

Try that change on the erroring environment and let us know if that produces a successful query.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]