Solved

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

Posted on 2016-08-26
5
30 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 36

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

19 Experts available now in Live!

Get 1:1 Help Now