Solved

Data Comparison ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Posted on 2016-08-26
5
42 Views
Last Modified: 2016-09-27
I am running this query. It run fine when the date comparison are commented out  but as soon as I uncomment any of the date comparisons in the where  clause I get an error ORA-01841: (full) year must be between -4713 and +9999, and not be 0

I do not understand why the query works when the date comparisons are commented out  when I am using the same equation in 2 different places in the query to convert the name to a date



SELECT    TO_DATE( NAME||'-01', 'YYYY-MM-DD') SPD_IDENTIFIED_DT
            , EXTRACT(month FROM TO_DATE( NAME||'-01', 'YYYY-MM-DD'))IDENTIFIED_MONTH
            , EXTRACT(Year FROM TO_DATE( NAME||'-01', 'YYYY-MM-DD')) IDENTIFIED_YEAR
    FROM POPULATION
    WHERE client_code = 'HN'
    AND population_type = 'SPD_ACTIVE'
    AND name NOT LIKE '%Y%'
    AND name IS NOT NULL
 --  AND TO_DATE( NAME||'-01', 'YYYY-MM-DD') > TO_DATE('2013-01-31', 'YYYY-MM-DD' )
  -- AND EXTRACT(Year FROM TO_DATE( NAME||'-01', 'YYYY-MM-DD'))  <> EXTRACT(YEAR FROM SYSDATE)
  -- AND  EXTRACT(MONTH FROM (TO_DATE( NAME||'-01', 'YYYY-MM-DD')))  = EXTRACT(MONTH FROM SYSDATE)

Here are my values for Name in the database
NAME
2011-06
2011-07
2011-08
2011-09
2011-10
2011-11
2011-12
2012-01
2012-02
2012-03
2012-04
2012-05
2012-06
2012-07
2012-08
2012-09
2012-10
2012-11
2012-12
2013-01
2013-02
2013-03
2013-04
2013-05
2013-06
2013-07
2013-08
2013-09
2013-10
2013-11
2013-12
2014-01
2014-02
2014-03
2014-04
2014-05
2014-06
2014-07
2014-08
2014-09
2014-10
2014-11
2014-12
2015-01
2015-02
2015-03
2015-04
2015-05
2015-06
2015-07
2015-08
2015-08
2015-09
2015-10
2015-11
2015-12
2016-01
2016-02
2016-03
2016-04
2016-05
2016-06
2016-07
2016-08
0
Comment
Question by:Dana Fredriksson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 125 total points
ID: 41772251
Given the sample data you have posted, it will not produce that result.  Given the where clause in your query, I would assume that there is data other than dates in there.  What I would do is to create a function like this one:
CREATE OR replace FUNCTION Val_date (d VARCHAR2) 
RETURN NUMBER 
AS 
  d_dt DATE; 
BEGIN 
    d_dt := To_date(d, 'yyyy-mm'); 

    RETURN ( 1 ); 
EXCEPTION 
  WHEN OTHERS THEN 
             RETURN 0; 
END; 

/ 

Open in new window

Then run:

select * from population where val_date(name) = 0;

That should show you the rows that are failing the conversion to date.

Also,  you don't need to add a day of the month.  This does that same thing:

sELECT    TO_DATE( NAME, 'YYYY-MM') SPD_IDENTIFIED_DT
            , EXTRACT(month FROM TO_DATE( NAME, 'YYYY-MM'))IDENTIFIED_MONTH
            , EXTRACT(Year FROM TO_DATE( NAME, 'YYYY-MM')) IDENTIFIED_YEAR
    FROM POPULATION
...;

Open in new window

1
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 125 total points
ID: 41774354
bad design gives bad problems

add a date column to the table and use that instead
1
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
ID: 41775072
Whenever you force Oracle to do "to_date" conversions on data that is stored in a column that is not a "DATE" column, you are at risk of getting the ORA-01841 (or a similar, date-related) error.  Sooner or later someone (or some program) will introduce data into that column that does not fit your expected format, or with an invalid value like "2016-13".  Then your query will fail.

Ideally, information that will be used as dates is stored in columns that are defined as "DATE" columns in the database.  

Use a PL\SQL function like the one johnsone suggested to identify the problem row(s) then update it/them to valid value(s) or delete it/them.  If possible, add a constraint or a trigger to insure that future rows all contains valid values.  If that is not possible, then you will always need to use a PL\SQL function like that in your "where" clause, but then with "val_date(name) = 1".  This will cause any rows with invalid values to be ignored, but it will allow your query to run.
1
 
LVL 8

Assisted Solution

by:Ghunaima
Ghunaima earned 125 total points
ID: 41780337
Why converting string to date & then back to string where you are comparing years & months. Try the below query. Also check that NAME column is valid in all the records

SELECT name FROM POPULATION  
WHERE client_code = 'HN'
    AND population_type = 'SPD_ACTIVE'
    AND name NOT LIKE '%Y%'
    AND name IS NOT NULL
   AND (SUBSTR(TRIM(NAME), 6,2)  NOT BETWEEN '01' AND '12'
             OR SUBSTR(TRIM(NAME), 1,4) NOT BETWEEN 1900 AND '3000'
             OR SUBSTR(TRIM(NAME), 5,1)<>'-')
order by 1;

SELECT DISTINCT SUBSTR(TRIM(NAME), 6,2) YR FROM POPULATION  
WHERE client_code = 'HN'
    AND population_type = 'SPD_ACTIVE'
    AND name NOT LIKE '%Y%'
    AND name IS NOT NULL
order by 1;


SELECT  TRUNC(TO_DATE( trim(NAME), 'YYYY-MM')) SPD_IDENTIFIED_DT
            , to_char(TRUNC(TO_DATE( trim(NAME), 'YYYY-MM')), 'MM') IDENTIFIED_MONTH
            ,  to_char(TRUNC(TO_DATE( trim(NAME), 'YYYY-MM')), 'YYYY') IDENTIFIED_YEAR
    FROM POPULATION
    WHERE client_code = 'HN'
    AND population_type = 'SPD_ACTIVE'
    AND name NOT LIKE '%Y%'
    AND name IS NOT NULL
 AND TO_DATE( TRIM(NAME)||'-01', 'YYYY-MM-DD') > TO_DATE('2013-01-31', 'YYYY-MM-DD' )
 AND TRIM(NAME) like TO_CHAR(SYSDATE, 'yyyy')||'%'
 AND  TRIM(NAME) like '%'||TO_CHAR(SYSDATE, 'mm')
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question