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>
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>
what about leap years ?
ReplyDeleteSQL> 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