-1

I am getting an error "(full) year must be between -4713 and +9999, and not be 0" unable to figure why this error is coming.

SELECT B.TST_CTR_CDE, C.DESCRIPTION,TO_CHAR(TO_DATE(B.CMPLT_DTE,'yyyymmdd'), 'MONTH') Month,
sum(decode(b.pass_sta,'1',1)) as passed,
sum(decode(b.pass_sta,'0',1)) as failed,
sum(decode(b.pass_sta, '0',1,'1',1)) as tot, 
extract(month from TO_DATE(B.CMPLT_DTE,'yyyymmdd')) Mon, 
extract(year from TO_DATE(B.CMPLT_DTE,'yyyymmdd')) year 

 FROM aei052 A,
aei053 B,
 aei006 C
 WHERE
 A.MNT_ACTN_CDE='A' 
 AND
 A.EXAMNE_SID=B.EXAMNE_SID
 AND
 TO_DATE(B.CMPLT_DTE,'yyyymmdd') BETWEEN TO_DATE('07/01/2012','mm/dd/yyyy') AND TO_DATE ('06/30/2013','mm/dd/yyyy')
AND B.CMPLT_STA=1 AND
B.TST_CTR_CDE = C.TST_CTR_ID AND rownum <10
GROUP BY B.TST_CTR_CDE,C.DESCRIPTION, 
TO_CHAR(TO_DATE(B.CMPLT_DTE,'yyyymmdd'), 'MONTH'), extract(year from TO_DATE(B.CMPLT_DTE,'yyyymmdd')),extract(month from TO_DATE(B.CMPLT_DTE,'yyyymmdd'))
ORDER BY TST_CTR_CDE, YEAR, MON;
tom
  • 5,114
  • 6
  • 24
  • 36
  • 2
    What are the distinct values of `extract(year from TO_DATE(B.CMPLT_DTE,'yyyymmdd')`? `SELECT distinct extract(year from TO_DATE(B.CMPLT_DTE,'yyyymmdd') from table2 B`... any 0 values or ones less than -4713 or greater than 9999? – xQbert May 27 '15 at 18:57

1 Answers1

1

Obviously, the problem is: TO_DATE(B.CMPLT_DTE,'yyyymmdd').

The following query should help you find the troublesome values:

select b.cmplt_dte
from table2 B
where length(b.cmplt_dte) <> 8 or
      substr(b.complt_dte, 4) not between '1900' and '2100';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786