ORA-01841 error when manipulating strings representing dates

OK, I'm completely stumped on this one.

I have a table that contains a date stored as a CHAR(8 CHAR) in the format YYYYMMDD.  It is a date representative of the last date a maintenance occurred on a piece of equipment.
There's another column which is a scheduled frequency, which is NUMBER(7).

(It's a 3rd party app, we have no control over the data types presented before someone says "Make the column a date!").

The query is trying return rows based on last scheduled date + frequency being in a certain range.

(I've cut out all the where clauses for brevity).

If I run this:
SELECT last_sch_date, sched_freq_1,
to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') as date_plus_freq
from table where blah blah blah

Open in new window

I get this:
LAST_SCH SCHED_FREQ_1 DATE_PLU
-------- ------------ --------
20150608           30 20150708
20090522           28 20090619
20150624           30 20150724
20150624           30 20150724
20140922           30 20141022
20140922           30 20141022
20150628           28 20150726
20150412           28 20150510
20150609           30 20150709
20110315           30 20110414
20150628           30 20150728
20150628           28 20150726
20150628           28 20150726

13 rows selected.

Open in new window

Adding in this bit
select * from
(SELECT last_sch_date, sched_freq_1,
to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') as date_plus_freq
from table where blah blah blah)
WHERE date_plus_freq between '20150701' and '20150730'

Open in new window


gets me:
to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') as date_plus_freq
                *
ERROR at line 5:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Open in new window


I have thought that there may be bad data in the last_sch_date column - when I looked at the contents of just that one column for all rows returned with the base where clause, all contain data and every row meets the YYYYMMDD mask (and is a valid date).  There are some rows in the table outside the scope of the WHERE clause where the date column is spaces or contains '00000000' - but these are specifically excluded in the where clause by LAST_SCH_DATE > '00000000'.  You can also see the result set contains no invalid data.

I've also tried:
select * 
from
(
SELECT last_sch_date, sched_freq_1,
to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1 as date_plus_freq
FROM Table
WHERE  blah blah blah
)
WHERE date_plus_freq between to_date('20150701','YYYYMMDD') and to_date('20150730','YYYYMMDD')

Open in new window


Further, I've also tried it this way:

SELECT *
FROM Table
WHERE  blah blah blah
  AND to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') >= '20150701'
  AND to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') <= '20150730'

Open in new window


I get exactly the same result.

The bafflement is strong with this one.

Anyone have any ideas ?
LVL 23
Steve WalesSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Based on the data provided, it works for me...

I added 'bad data' to my test case.

Running 11.2.0.3

drop table tab1 purge;
create table tab1(LAST_SCH_DATE char(8), SCHED_FREQ_1 number(7));

insert into tab1 values('20150608',30);
insert into tab1 values('20090522',28);
insert into tab1 values('20150624',30);
insert into tab1 values('20150624',30);
insert into tab1 values('20140922',30);
insert into tab1 values('20140922',30);
insert into tab1 values('20150628',28);
insert into tab1 values('20150412',28);
insert into tab1 values('20150609',30);
insert into tab1 values('20110315',30);
insert into tab1 values('20150628',30);
insert into tab1 values('20150628',28);
insert into tab1 values('20150628',28);
--outlier
insert into tab1 values('99999999',28);
commit;


SELECT last_sch_date, sched_freq_1,
to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') as date_plus_freq
from tab1 where last_sch_date like '2%'
/


select * from
(SELECT last_sch_date, sched_freq_1,
to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') as date_plus_freq
from tab1 where last_sch_date like '2%')
WHERE date_plus_freq between '20150701' and '20150730'
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
Just for grins:
create table mytesttab as
SELECT last_sch_date, sched_freq_1,
to_char(to_date(LAST_SCH_DATE, 'YYYYMMDD') + sched_freq_1, 'YYYYMMDD') as date_plus_freq
from table where blah blah blah

Then try the other queries from the new table.
0
Steve WalesSenior Database AdministratorAuthor Commented:
Yeah tried those scenarios already - running CTAS to create a table - or creating from scratch, it works just fine.

When using the data that is present already, I get the error.

Since the base select without the date boundaries returns 13 valid rows, I just don't know what to do with this one now.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Is there an index on any of the columns in the query?

The WAG is possibly index corruption?

Second guess:
Bounce the database.  Reboots have fixed many an unexplained error...
0
Steve WalesSenior Database AdministratorAuthor Commented:
To further throw a wrench into this one.

There's another column called work group on this table.  It defines which area is doing the maintenance.

There are 13 rows, spread across 5 work groups.

If I add in work group to the where and select them one at a time, they all work.  Take work group out and it blows up again.

Execution plan shows no index access on this table - it's doing "TABLE ACCESS STORAGE FULL" - it's an Exadata.
0
slightwv (䄆 Netminder) Commented:
I had an issue many years back that whenever I selected a specific row in a table I would get an ora-03113.  I grabbed the info from the table I could, deleted and reinserted the row and everything was good from then on.

Can you move the table to a different tablespace and move it back?  Maybe it just needs to relocate some blocks?

>>it's an Exadata.

Must be nice to have a TON of $$$!!!!!

I went to a seminar/sales pitch on Exadata recently.  They are really proud of their engineering of older technology, aren't they!!!
0
Steve WalesSenior Database AdministratorAuthor Commented:
Not my machine :)  One of my customers.

I'm also wondering if there's a possible data corruption.

Moved the table out and back - same result.

Going to ask their DBA to run utlvalid / utlchn1 just for giggles and then I'll try analyze table ... validate structure - just to make sure there are no invalid blocks (although I would have thought that if there were, the move might have corrected them - but maybe there's an issue in a block somewhere).    This is just weird.
0
PortletPaulfreelancerCommented:
A:
FROM Table
WHERE  blah blah blah

B:
the date column is spaces or contains '00000000'

C:
ERROR at line 5:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


I'd suggest something in A is allowing B to be evaluated by to_date() function resulting in C
perhaps there is an OR in that blah blah?


Perhaps move some of the needed logic into a case expression to help avoid the issue, e.g.

select
    LAST_SCH, SCHED_FREQ_1, LAST_SCH + SCHED_FREQ_1
from (
  select case when LAST_SCH > '00000000' then to_date(LAST_SCH,'YYYYMMDD') end as LAST_SCH , SCHED_FREQ_1
  from table1
  )
 ;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
I bet there are constraints on the table where you can't CTAS, truncate/delete, insert?

If there aren't many, could you modify them to deferrable, delete, insert, commit, then change them back?  Never tried that but in theory it should be possible.

I'm beginning to think that we are WAY over thinking this and we'll both go DUH when it is finally figured out.
0
PortletPaulfreelancerCommented:
in case its useful
**Oracle 11g R2 Schema Setup**:

    
    CREATE TABLE Table1
        (LAST_SCH char(8), SCHED_FREQ_1 int, "DATE_PLU" int)
    ;
    
    INSERT ALL 
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('00000000', 30, 20150708)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES (NULL, 30, 20150708)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150608', 30, 20150708)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20090522', 28, 20090619)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150624', 30, 20150724)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150624', 30, 20150724)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20140922', 30, 20141022)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20140922', 30, 20141022)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150628', 28, 20150726)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150412', 28, 20150510)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150609', 30, 20150709)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20110315', 30, 20110414)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150628', 30, 20150728)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150628', 28, 20150726)
        INTO Table1 ("LAST_SCH", "SCHED_FREQ_1", "DATE_PLU")
             VALUES ('20150628', 28, 20150726)
    SELECT * FROM dual
    ;
**Query 1**:

    select
        LAST_SCH, SCHED_FREQ_1, LAST_SCH + SCHED_FREQ_1
    from (
      select case when LAST_SCH > '00000000' then to_date(LAST_SCH,'YYYYMMDD') end as LAST_SCH , SCHED_FREQ_1
      from table1
      )
     

**[Results][2]**:
    |                    LAST_SCH | SCHED_FREQ_1 |     LAST_SCH+SCHED_FREQ_1 |
    |-----------------------------|--------------|---------------------------|
    |                      (null) |           30 |                    (null) |
    |                      (null) |           30 |                    (null) |
    |      June, 08 2015 00:00:00 |           30 |    July, 08 2015 00:00:00 |
    |       May, 22 2009 00:00:00 |           28 |    June, 19 2009 00:00:00 |
    |      June, 24 2015 00:00:00 |           30 |    July, 24 2015 00:00:00 |
    |      June, 24 2015 00:00:00 |           30 |    July, 24 2015 00:00:00 |
    | September, 22 2014 00:00:00 |           30 | October, 22 2014 00:00:00 |
    | September, 22 2014 00:00:00 |           30 | October, 22 2014 00:00:00 |
    |      June, 28 2015 00:00:00 |           28 |    July, 26 2015 00:00:00 |
    |     April, 12 2015 00:00:00 |           28 |     May, 10 2015 00:00:00 |
    |      June, 09 2015 00:00:00 |           30 |    July, 09 2015 00:00:00 |
    |     March, 15 2011 00:00:00 |           30 |   April, 14 2011 00:00:00 |
    |      June, 28 2015 00:00:00 |           30 |    July, 28 2015 00:00:00 |
    |      June, 28 2015 00:00:00 |           28 |    July, 26 2015 00:00:00 |
    |      June, 28 2015 00:00:00 |           28 |    July, 26 2015 00:00:00 |


[1]: http://sqlfiddle.com/#!4/99c82/7

Open in new window

0
Steve WalesSenior Database AdministratorAuthor Commented:
Paul, I don't know WHY your solution works ...

But it does.

That's all I needed :)

Thanks!

(Thanks for you contributions as well, Steve)
0
PortletPaulfreelancerCommented:
No worries Steve (Wales), it sometimes just needs another set of eyes to make the right suggestion

However it's a very rare day when I spot something that slightwv hasn't
0
slightwv (䄆 Netminder) Commented:
>>However it's a very rare day when I spot something that slightwv hasn't

It happens all the time!!!  I couldn't figure out how 'bad' data was sneaking in past the where clause.
0
PortletPaulfreelancerCommented:
>>"I couldn't figure out how 'bad' data was sneaking in past the where clause."

between the second and third "blah"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.