Solved

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

Posted on 2016-08-26
5
33 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
5 Comments
 
LVL 34

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXECUTE IMMEDIATE 5 66
oracle RMAN - trying to duplicate a database 5 28
PL/SQL Two changes 7 27
dbms_crypto.decrypt   errors out 6 31
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now