Thursday, March 08, 2012

ORA-01841

There was no year 0 in the calendar and Oracle's to_date function recognises this but for some reason it rejects -4713. If anybody knows why, perhaps they could add a comment below: 

SQL> select
  2  to_char(to_date('-4714','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-4714','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('-4713','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-4713','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('-4712','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
4712 BC

SQL> select
  2  to_char(to_date('-1','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
0001 BC

SQL> select
  2  to_char(to_date('-0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('+0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('+0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('1','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
0001 AD

SQL> select
  2  to_char(to_date('9999','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
9999 AD

SQL>

1 comment:

Laurent Schneider said...

what about leap years ?


SQL> select date '-4708-02-29' from dual;
DATE'-4708-02-29'
-------------------
4708-02-29_00:00:00

SQL> select date '-4712-02-29' from dual;
select date '-4712-02-29' from dual
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month


there is more than one discussion about year 0