Solved

ORA-01841: (full) year must be between -4713 and +9999, and not be 0  ONLY when selecting field from joined table

Posted on 2011-02-25
31
2,890 Views
Last Modified: 2012-05-11
I run the queries below and get the same results but only when I select a field from the joined table do I get an error, same number of rows returned but an error.

Situation 1
run query without joined table
results 3600 rows NO error

Situation 2
run query with joined table NOT selecting field from joined table
result 3600 rows NO error

Situation 3
run query with joined table selecting field from joined table
results 3600 rows with error

NO differences in the dates in any query
Dates are all from Table A none from Table B





select  a.supplier_no,
to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') "PAID DATE",
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')"APPROVED DATE",
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')+ 3 "APPROVED DATE + 3"
from Ellipse.MSF260 a
where  paid_date between '20100701' and '20110228'
and to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3
and paid_date is not null
and paid_date <> ' '
and approved_date is not null
and approved_date <> ' ';

3600 rows selected   NO ERROR

select  a.supplier_no,
to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') "PAID DATE",
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')"APPROVED DATE",
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')+ 3 "APPROVED DATE + 3"
from Ellipse.MSF260 a left outer join Ellipse.MSF200 b
on  a.supplier_no = b.supplier_no
where  paid_date between '20100701' and '20110228'
and to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3
and paid_date is not null
and paid_date <> ' '
and approved_date is not null
and approved_date <> ' ';

3600 rows selected   NO ERROR




 
select  a.supplier_no,  b.supplier_name,
to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') "PAID DATE",
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')"APPROVED DATE",
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')+ 3 "APPROVED DATE + 3"
from Ellipse.MSF260 a left outer join Ellipse.MSF200 b
on  a.supplier_no = b.supplier_no
where  paid_date between '20100701' and '20110228'
and to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3
and paid_date is not null
and paid_date <> ' '
and approved_date is not null
and approved_date <> ' ';

3600 rows selected
ERROR  ORA-01841: (full) year must be between -4713 and +9999, and not be 0
0
Comment
Question by:glendawi
  • 13
  • 10
  • 7
  • +1
31 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985517
Unless I missed it, I don't see the difference between the last two queries except for adding "b.supplier_name,"

Also you should get 3600 rows selected then an error.

The error will be during the parse.  Either an error or rows selected.

Double check the code you are running.

NOW, what I do see is a potential syntax issue with the following:

...
to_date((nvl(approved_date,'1900-01-01')),'YYYY-MM-DD')"APPROVED DATE",
...
between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3
...


You are using two different masks for the approved_date column.  'YYYY-MM-DD' in the first part and 'YYYYMMDD' in the second.

Which one is it?


0
 

Author Comment

by:glendawi
ID: 34985723
I'll check on the format mask for approved date.
You're right there is no difference between situation 2 and 3 except adding b.supplier_name, and situation 3 draws an error but situation 2 does not.
What do you mean by

"Also you should get 3600 rows selected then an error.

The error will be during the parse.  Either an error or rows selected"
why should I get an error?
.

Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34985731
What select generates the: ERROR  ORA-01841: (full) year must be between -4713 and +9999, and not be 0

0
 

Author Comment

by:glendawi
ID: 34985746
Only the select where I include b.supplier_name in the query.  That's the only difference
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34985855
you don't need the is not null checks for paid_date and approved_date,  that's implicit in the other conditions

also, as I showed in your previous question,  you don't need the nvl of paid_date either


to_date(paid_date,'YYYY-MM-DD')  between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3

is sufficient


>>>  The error will be during the parse.  Either an error or rows selected.

the ora-1841 is a runtime error, not a parsing error,  but I do agree you'll either get results or an error, not both.

if you are getting results AND an error, then the error must be coming from something that executes after the results are returned
0
 
LVL 20

Expert Comment

by:flow01
ID: 34986717
A wild guess:
Are Ellipse.MSF260 and Ellipse.MSF200 real tables or views , maybe containing a pl/sql-function ?
A  pl/sql-function might try a date-conversion : dbms_output the resulting error (is dbms_output active) and still return a default value.
In that case results plus an error message could be possible.
Accessing a column in the b.table can lead to another access-path evaluating records that otherwise would not be selected.

Are de date columns defined as date in the table/views ?
to_date((nvl(paid_date,'1900-01-01')),'YYYY-MM-DD') "PAID DATE",    -- suggests an non date-field of format yyyy-mm-dd
where  paid_date between '20100701' and '20110228'                      -- suggests an non date-field of format yyyymmdd
0
 

Author Comment

by:glendawi
ID: 34987388
They are tables not views  (I will double check)
dates are char fields that needed to be converted
     Strangely if I hard code the dates, I do not get the error (previous thread)
I did the null checks because I was getting desperate.  I'll try it without them also with a consistent date format and let you know if it helps.
Thanks
0
 

Author Comment

by:glendawi
ID: 34993691
I removed the nvl and made a consistent date format.  Still 3600 rows with an error?
Any ideas?  
How would I know if I had the "wrong" date format?  The original data is YYYMMDD.  The SQL Plus default is DD-MON-YY.  Could this be it?
Thanks


select a.dstrct_code,  a.supplier_no,  supplier_name, contract_no, a.inv_no, a.amt_paid_for, a.sd_amount, a.currency_type,
to_date(paid_date,'YYYY-MM-DD') "PAID DATE",
to_date(approved_date,'YYYY-MM-DD')"APPROVED DATE",
to_date(approved_date,'YYYY-MM-DD')+ 3 "APPROVED DATE + 3"
from Ellipse.MSF260 a left outer join Ellipse.MSF200 b
on  a.supplier_no = b.supplier_no
where  paid_date between '20100701' and '20110228'
and to_date(paid_date,'YYYY-MM-DD') between to_date(approved_date,'YYYY-MM-DD') and to_date(approved_date,'YYYY-MM-DD') +3
and paid_date is not null
and paid_date <> ' '
and approved_date is not null
and approved_date <> ' ';

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



3600 rows selected.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34994331
to_date(paid_date,'YYYY-MM-DD')

where  paid_date between '20100701' and '20110228'


this doesn't make sense.  if paid_date is in yyyy-mm-dd format,  don't compare it to yyyymmdd format
0
 

Author Comment

by:glendawi
ID: 34994412
Thanks, still getting the results and the error  


select a.dstrct_code,  a.supplier_no,  supplier_name, contract_no, a.inv_no, a.amt_paid_for, a.sd_amount, a.currency_type,
to_date(paid_date,'YYYYMMDD') "PAID DATE",
to_date(approved_date,'YYYYMMDD')"APPROVED DATE",
to_date(approved_date,'YYYYMMDD')+ 3 "APPROVED DATE + 3"
from Ellipse.MSF260 a left outer join Ellipse.MSF200 b
on  a.supplier_no = b.supplier_no
where  paid_date between '20100701' and '20110228'
and to_date(paid_date,'YYYYMMDD') between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3
and paid_date is not null
and paid_date <> ' '
and approved_date is not null
and approved_date <> ' ';
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34996899
It's very bad to rely on Oracle to do implicit conversions for you fir this very reason.

Strings are strings.  Dates are dates.  If you need a specific data type, manually convert it.

I still see:  where  paid_date between '20100701' and '20110228'


You should use to_date on those as well.

You also still have all the 'null' checks where you don't need them as sdstuber mentioned.  If you insist on keeping them at least remove the 'space' check and go with  "and trim(approved_date) is not null" for both checks.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34996945
Also every date format was changed in the most recent query.

If every column is a string type and all strings have formats of YYYYMMDD  that's fine.
If some have other formats, then use those.

Also,  are you doing ANYTHING else besides running that query?

You log in with sql plus, run that query (no automatic scripts like login.sql or glogin.sql or other commands)
and then exit and still receive that error and rows returned?

If you are doing anything at all (intentional or not, trivial or not) please specify
0
 

Author Comment

by:glendawi
ID: 34998346
 I removed all the  "is null", is <> ""
I changed the format on the between to to_date
first just on the date
then on the paid date


where  paid_date between to_date('20100701', 'YYYYMMDD') and to_date('20110228', 'YYYYMMDD')
ORA-01861: literal does not match format string
No rows returned

where  to_date(paid_date, 'YYYYMMDD') between to_date('20100701', 'YYYYMMDD') and to_date('20110228', 'YYYYMMDD')
ERROR at line 7:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
No rows returned
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34998537
>> to_date(paid_date, 'YYYYMMDD')

You must have a 'paid_date' value in the database that does not match that format.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34998548
some of your columns are dates and some are strings.  You are treating them all as strings.

Without a describe of Ellipse.MSF260 and Ellipse.MSF200 it's impossible to say which are which.

Don't use TO_DATE on a date type or timestamp type
0
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.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34998611
To repeat some and re-comment on others:

Strings are strings.  
Dates are dates.  
You should not mix and match.  
You should not let Oracle convert one to the other for you.

If you need a string from a date:  to_char
If you need a date from a string: to_date
0
 

Author Comment

by:glendawi
ID: 34998666
APPROVED_DATE                             NOT NULL CHAR(8)
LOADED_DATE                               NOT NULL CHAR(8)
DUE_DATE                                  NOT NULL CHAR(8)
PAID_DATE                                 NOT NULL CHAR(8)

They are strings, so I'm using to_date.  That's correct, yes?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34998685
>>That's correct, yes?

it depends.

If you want to do date comparisions then yes.  use to_date.

If you want to do string comparisons (ascii) then no.

It all depends on what you want.

If the data is ALL YYYYMMDD then an ascii sort/between should be fine.

The problem you have by the errors above is ALL you data is NOT in that format.

 
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34998705
or is in that format, but is invalid.


'00000000'  is in yyyymmdd format,  but it's an invalid date
0
 

Author Comment

by:glendawi
ID: 34999297
I ran the below to check for a valid date in both paid_date and approval_date.
I got 1,585 records with
APPROVED
--------

That's why I originally had in
where approved_date <> ' "

Otherwise, it looks like my paid and approved dates are all in the 'YYYYMMDD' format

SELECT distinct approved_date FROM Ellipse.MSF260 WHERE
 CASE WHEN LENGTH(approved_date) = 8 THEN
  CASE WHEN TRANSLATE(SUBSTR(approved_date,1,4),'0123456789','NNNNNNNNNN') = 'NNNN' THEN
   CASE WHEN TO_NUMBER(SUBSTR(approved_date,1,4)) BETWEEN 1900 AND 2100 THEN
     CASE WHEN TRANSLATE(SUBSTR(approved_date,5,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
      CASE WHEN TO_NUMBER(SUBSTR(approved_date,5,2)) BETWEEN 1 AND 12 THEN
        CASE WHEN TRANSLATE(SUBSTR(approved_date,7,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
         CASE WHEN TO_NUMBER(SUBSTR(approved_date,7,2)) BETWEEN 1 AND 31 THEN
          'Y'
         END END END END END END END
 IS NULL
and approved_date is not null
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34999313
You originally had "and approved_date <> ' ';"

not "and approved_date <> 'APOROVED';"

Those are two different things.

You also didn't add TRIM to my code when I suggested to remove the "<> ' '" code.

0
 

Author Comment

by:glendawi
ID: 35000278
I agree I probably have an invalid date, but I can't seem to find it
Error on code;
select a.dstrct_code,  a.supplier_no,  supplier_name, contract_no, a.inv_no, a.amt_paid_for, a.sd_amount, a.currency_type,
to_date(paid_date,'YYYYMMDD') "PAID DATE",
to_date(approved_date,'YYYYMMDD')"APPROVED DATE",
to_date(approved_date,'YYYYMMDD')+ 3 "APPROVED DATE + 3"
from Ellipse.MSF260 a left outer join Ellipse.MSF200 b
on  a.supplier_no = b.supplier_no
where  to_date(paid_date, 'YYYYMMDD') between to_date('20100701', 'YYYYMMDD') and to_date('20110228', 'YYYYMMDD')
and to_date(paid_date, 'YYYYMMDD') between to_date(approved_date,'YYYYMMDD') and to_date(approved_date,'YYYYMMDD') +3
and SUBSTR(paid_date,7,2) <> ' '
and trim(paid_date) is not null
and paid_date <> ' '
and trim(approved_date) is not null
and approved_date <> ' ';

where  to_date(paid_date, 'YYYYMMDD') between to_date('20100701', 'YYYYMMDD') and to_date('20110228', 'YYYYMMDD')
               *
ERROR at line 7:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Validation of dates:
SELECT distinct * FROM Ellipse.MSF260 WHERE
 CASE WHEN LENGTH(paid_date) = 8 THEN
  CASE WHEN TRANSLATE(SUBSTR(paid_date,1,4),'0123456789','NNNNNNNNNN') = 'NNNN' THEN
   CASE WHEN TO_NUMBER(SUBSTR(paid_date,1,4)) BETWEEN 1900 AND 2100 THEN
     CASE WHEN TRANSLATE(SUBSTR(paid_date,5,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
      CASE WHEN TO_NUMBER(SUBSTR(paid_date,5,2)) BETWEEN 1 AND 12 THEN
        CASE WHEN TRANSLATE(SUBSTR(paid_date,7,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
         CASE WHEN TO_NUMBER(SUBSTR(paid_date,7,2)) BETWEEN 1 AND 31 THEN
          'Y'
         END END END END END END END
 IS NULL
and paid_date is not null
and paid_date <> ' '
no rows selected

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35000380
Just create a simple loop to weed out the 'bad' data.

Check the following test case.  You can adapt the loop for your tables.
drop table tab1 purge;
create table tab1(paid_date char(8));

insert into tab1 values('20111111');
insert into tab1 values('2O111111');
insert into tab1 values('Bob');
commit;


declare
	junk date;
begin

	for i in (select paid_date from tab1) loop
	begin
		junk := to_date(i.paid_date,'YYYYMMDD');
		exception when others then
			dbms_output.put_line('Bad date: ' || i.paid_date);
	end;
	end loop;
end;
/

Open in new window

0
 

Author Comment

by:glendawi
ID: 35000725
PAID_DAT
--------
20111111
2O111111
Bob
20111111
2O111111
Bob

there's a 'O' instead of a zero!
0
 

Author Comment

by:glendawi
ID: 35000745
nope, that's what we entered.  
SQL> declare
  2   junk date;
  3  begin
  4  
  5   for i in (select paid_date from ellipse.MSF260) loop
  6   begin
  7    junk := to_date(i.paid_date,'YYYYMMDD');
  8    exception when others then
  9     dbms_output.put_line('Bad date: ' || i.paid_date);
 10   end;
 11   end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>

does this mean that all the dates are good?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35000746
Bob is also an invalid date.  :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35000763
>>there's a 'O' instead of a zero!

and?  I know.  That is my test case to show you how it works.  You need to take the PL/SQL loop and point it against your tables.

>>does this mean that all the dates are good?

if you have serveroutput set to on.  Now check the other date field in quetion.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35000769
check the MSF200  table too
0
 

Author Comment

by:glendawi
ID: 35000917
I turned serveroutput on
I got many lines of"Bad date: and then the rest.
How do I find what rows have the bad date or what the bad date is?
You are being wonderful and patcient.  I really appreciate it

Bad date:
Bad date:
Bad date:
Bad date:
Bad date:
Bad date:
Bad date:
Bad date:
declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 9
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35001716
>>ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

try (newer releases of Oracle): set serveroutput unlimited

>>Bad date:

Looks like you have some 'blank' entries.  This is where adding the:  'and trim(paid_date) is not null'
to the where clause comes in (trim for both dates values in the where clause).
0
 

Author Closing Comment

by:glendawi
ID: 35003071
I had to add "trim" to the between fields as well.  I ended up with twice as many rows as before.  Thanks so much for all your help
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

Suggested Solutions

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

15 Experts available now in Live!

Get 1:1 Help Now