Skip to Main Content
  • Questions

  • INSERT data in TIMESTAMP column having year less than 4713 i.e. timestamp like '01/01/8999 BC'

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: June 10, 2021 - 7:45 am UTC

Last updated: June 10, 2021 - 1:10 pm UTC

Version: 11G(11.2.0.1)

Viewed 1000+ times

You Asked

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

and Chris said...

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).

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.