Want to insert data in TIMESTAMP column having year less than 4713 i.e. timestamp like '01/01/8999 BC'
so here year is -8999
When I tries to insert it gives me error like 'YEar should be between -4713 and 9999'.
BUt for some table I am having year less than 4713 as well like -8999 and -78888
So this limit is not applicable to timestamp data type.
But then How to insert into timestamp for year -8999
The limit of 4712 BC applies to timestamp values as well as dates:
select timestamp'-4712-01-01 00:00:00' from dual;
TIMESTAMP'-4712-01-0100:00:00'
01-JAN-4712 00.00.00.000000000
select timestamp'-4713-01-01 00:00:00' from dual;
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
If it looks like you have values stored before this date, either:
- The column is not a date or timestamp
- There's a conversion error displaying the value
- The data from a client that doesn't check the date bounds. As MOS note 331831.1 explains:
The issue here comes from the way the client side code and the database communicate with each other. Depending on the level at which the connection is made, Oracle does not always do a full check of the value that is inserted (this is by design, this is not a fault).
If you use standard SQL commands in an application like SQL*Plus, then something like this can never happen, because the layer that this works on has all the checks and bounds that are needed to stop this data from entering. If illegal byte sequences were inserted in a database in those circumstances, it would be a bug in Oracle software.
However, if your client application uses OCI or Pro*C (not JDBC thin driver), then you can use the DATE external data type in OCI and Pro*C to insert illegal values into the database. The DATE external data type is not checked for valid values by Oracle, it is left to the client side to make sure that what is inserted is a correct DATE.For more details on this see the note (331831.1).