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"
Last updated on MAY 23, 2017
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:
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms