Query Retrieving "Date Information" Embedded in Column Data (For Example Within an Object Name) Fails with: "ORA-01841: (full) year must be between -4713 and +9999, and not be 0"
(Doc ID 2013397.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
- Query retrieves date information from a column where it would not normally be recorded. Common examples are object names such as partitions or tablespaces, but any column could be involved. For example, a tablespace containing data from 22nd February 2015 might be named DATA_20150222 to manually record the date that this data was entered.
- On trying to extract the date, the query fails with: "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". For example, the following query tries to retrieve the tablespaces that are less than 15 days old based upon their names:
TRUNC(SYSDATE) - to_date(trim( SUBSTR(tablespace_name,6,8)),'YYYYMMDD') ddd
WHERE tablespace_name LIKE 'DATA_2015%'
AND TRUNC(sysdate) - to_date(trim( SUBSTR(tablespace_name,6,8)),'YYYYMMDD') > 15;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
The query in question has a predicate that directly extracts date information from the object name - in this case : to_date(trim( SUBSTR(tablespace_name,5,8)),'YYYYMMDD') (which skips to the 6th character in the string in the tablespace_name column (which is a varchar2) and extracts the next 8 characters, converting them into a date). This columne does not necessarily contain data which can always be converted and relies on other columns to identify the valid data.
The problem might be affected by altering the access path of the query such that certain predicates are applied before others.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document