Geek asks and answers

Oracle 11.1 error converting Julian days to DATE or TIMESTAMP

This recent post made me investigate Julian to Oracle date conversions, and I came across what, in my opinion, is a bug in Oracle 11.1. Test examples:

Case 1

SELECT TO_CHAR(TO_TIMESTAMP('0', 'J'), 'DD MON SYYYY') FROM DUAL

This should return "01 JAN -4713" as defined here , but instead causes an error

ORA-01854: julian date must be between 1 and 5373484

Case 2

SELECT TO_CHAR(TO_TIMESTAMP('1', 'J'), 'DD MON SYYYY') FROM DUAL

This should return “02 ​​JAN -4713” as an extension of the above (one day later than Julian zero date), but instead returns “01 JAN -4712” (shutdown a day less than a year).

Case 3

SELECT TO_CHAR(TO_TIMESTAMP('1721424', 'J'), 'DD MON SYYYY') FROM DUAL

"01 JAN 0001". ( ). 1 , , , 31 DEC -0001 ( ); ,

SELECT TO_CHAR(TO_TIMESTAMP('1721423', 'J'), 'DD MON SYYYY') FROM DUAL

:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

, Oracle .

( , TO_TIMESTAMP , TO_DATE).

- ,

  • Oracle?
  • 11.2?

.

, 11.2.

Cthulhu fhtagn.

10.2.0.4

+3
3

, Oracle, Oracle : " - 1, 4712 . .."

, , Julian 1 1 4712 , , 2 . , . ( , , , Oracle, .) , 31 4713 .

1 1, . .

Oracle:

Oracle , 4713 . .. -4712. , , 4713 . .. -4713. Oracle Julian , , 365- BC. . http://www.usno.navy.mil/USNO/astronomical-applications/astronomical-information-center/millennium.

3 - . , . . :

SQL> select to_date('0001-01-01', 'YYYY-MM-DD') 
    - to_date ('-0001-12-31', 'SYYYY-MM-DD') from dual;

TO_DATE('0001-01-01','YYYY-MM-DD')-TO_DATE('-0001-12-31','SYYYY-MM-DD')
-----------------------------------------------------------------------
                                                                    367


SQL> select months_between(to_date('0001-01-01', 'YYYY-MM-DD')
  2      , to_date ('-0001-12-31', 'SYYYY-MM-DD')) from dual;

MONTHS_BETWEEN(TO_DATE('0001-01-01','YYYY-MM-DD'),TO_DATE('-0001-12-31','SYYYY-MM-DD'))
---------------------------------------------------------------------------------------
                                                                             12.0322581

-, 0 - .

+2

, 11.2.0.1.0:

PHIL@PHILL11G2 > select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.04
PHIL@PHILL11G2 > SELECT TO_CHAR(TO_TIMESTAMP('0', 'J'), 'DD MON SYYYY') FROM DUAL;
SELECT TO_CHAR(TO_TIMESTAMP('0', 'J'), 'DD MON SYYYY') FROM DUAL
                            *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484


Elapsed: 00:00:00.00
PHIL@PHILL11G2 > SELECT TO_CHAR(TO_TIMESTAMP('1', 'J'), 'DD MON SYYYY') FROM DUAL;

TO_CHAR(TO_T
------------
01 JAN -4712

Elapsed: 00:00:00.00
PHIL@PHILL11G2 > SELECT TO_CHAR(TO_TIMESTAMP('1721424', 'J'), 'DD MON SYYYY') FROM DUAL;

TO_CHAR(TO_T
------------
01 JAN  0001

Elapsed: 00:00:00.00
PHIL@PHILL11G2 > SELECT TO_CHAR(TO_TIMESTAMP('1721423', 'J'), 'DD MON SYYYY') FROM DUAL;
SELECT TO_CHAR(TO_TIMESTAMP('1721423', 'J'), 'DD MON SYYYY') FROM DUAL
                            *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


Elapsed: 00:00:00.04
PHIL@PHILL11G2 >
+1

More articles:


All Articles