Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 50
  • Last Modified:

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

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
Dana Fredriksson
Asked:
Dana Fredriksson
4 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
Geert GruwezOracle dbaCommented:
bad design gives bad problems

add a date column to the table and use that instead
1
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
GhunaimaCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now