How do I insert a BC date into Oracle?

Just wondering how I would insert the date 01/01/4000BC into oracle

Have tried the following but to no avail

INSERT INTO person(birth_date) VALUES(TO_DATE('-4700/01/01', 'syyyy/mm/dd'))

I'm sure I've missed something pretty obvious so s开发者_开发技巧ome fresh eyes on this would really help.

Thanks!

I don't see the problem with your syntax. It works for me:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as xxx

SQL> CREATE TABLE tt (d DATE);

Table created

SQL> INSERT INTO tt VALUES (to_date('-4700/01/01','syyyy/mm/dd'));

1 row inserted

SQL> SELECT to_char(d, 'MM/DD/YYYY BC') FROM tt;

TO_CHAR(D,'MM/DD/YYYYBC')
-------------------------
01/01/4700 BC

SQL>

What is the exact issue you're having? What version of Oracle?

You do have to use the BC or SYYYY format specifier on output else you get incorrect dates:

SQL> select * from tt;

D
-----------
00-h:mm:ss

SQL> 

INSERT INTO person(birth_date)
 VALUES(TO_DATE('4000/01/01 BC', 'yyyy/mm/dd bc'))

try

INSERT INTO person(birth_date) VALUES(TO_DATE('-4700/01/01', 'yyyy/mm/dd'))

or you could use BC notation - see http://rwijk.blogspot.com/2008/10/year-zero.html