Error ORA-01873 when converting data

[Eriovaldo Andrietta]

Hello,

I am facing this issue running this command in 11.2.0.4.0 (also in 12c R2 I
got the same error)

SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD
HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL;
SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') +
NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL

ORA-01873: a precisão precedente do intervalo é pequena demais

I check and data convertion allow year until 9999
The number used in the statment is 2060.

What is wrong ?

Regards
Eriovaldo

[Jonathan Lewis]

Eriovaldo,

The largest value you can supply as an input to numtodsinterval(X,'second') is power(2,31)-1

Regards
Jonathan Lewis

________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Eriovaldo Andrietta <***@gmail.com>
Sent: 26 September 2018 04:48
To: ORACLE-L
Subject: Error ORA-01873 when converting data

Hello,

I am facing this issue running this command in 11.2.0.4.0 (also in 12c R2 I got the same error)

SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL;
SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL

ORA-01873: a precisão precedente do intervalo é pequena demais

I check and data convertion allow year until 9999
The number used in the statment is 2060.

What is wrong ?

Regards
Eriovaldo

--
http://www.freelists.org/webpage/oracle-l

[Eriovaldo Andrietta]

Thanks Jonathan,

Issue solved:

SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD
HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000/60, 'MINUTE') FROM
DUAL;

TO_TIMESTAMP('1970-01-0100:00:00.0','YYYY-MM-DDHH24:MI:SS.FF')+NUMTODSINTERVAL(2
--------------------------------------------------------------------------------
26/04/60 01:00:02,000000000

SQL>

Regards
Eriovaldo

Em qua, 26 de set de 2018 às 04:48, Jonathan Lewis <

Post by Jonathan Lewis
Eriovaldo,
The largest value you can supply as an input to
numtodsinterval(X,'second') is power(2,31)-1
Regards
Jonathan Lewis
________________________________________
Sent: 26 September 2018 04:48
To: ORACLE-L
Subject: Error ORA-01873 when converting data
Hello,
I am facing this issue running this command in 11.2.0.4.0 (also in 12c R2
I got the same error)
SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD
HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL;
SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') +
NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL
ORA-01873: a precisão precedente do intervalo é pequena demais
I check and data convertion allow year until 9999
The number used in the statment is 2060.
What is wrong ?
Regards
Eriovaldo
--
http://www.freelists.org/webpage/oracle-l