Oracle: year must be between -4713 and +9999, and not be 0

Question

I have an Oracle table like this

|---------------------------------|
|EMPNO |    HIREDATE  | INDEX_NUM |  
|---------------------------------|  
|1     |   2012-11-13 | 1         |
|2     |   2          | 1         |
|3     |   2012-11-17 | 1         |
|4     |   2012-11-21 | 1         |
|5     |   2012-11-24 | 1         |
|6     |   2013-11-27 | 1         |
|7     |   2          | 2         |
|---------------------------------|

I am trying to execute this query against this table

SELECT hiredate
  FROM admin_emp
  WHERE TO_DATE('hiredate','yyyy-mm-dd') >= TO_DATE('2012-05-12','yyyy-mm-dd');

But getting the error

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

Any idea..? What is the issue here?

query base:

CREATE TABLE admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         hiredate   VARCHAR(255),
         index_num NUMBER(5));


insert into admin_emp(empno,hiredate,index_num) values
(1,'2012-11-13',1);
insert into admin_emp(empno,hiredate,index_num) values
(2,'2',1);
insert into admin_emp(empno,hiredate,index_num) values
(3,'2012-11-17',1);
insert into admin_emp(empno,hiredate,index_num) values
(4,'2012-11-21',1);
insert into admin_emp(empno,hiredate,index_num) values
(5,'2012-11-24',1);
insert into admin_emp(empno,hiredate,index_num) values
(6,'2013-11-27',1);
insert into admin_emp(empno,hiredate,index_num) values
(7,'2',2);

Solution

Single quotes (') in SQL denote string literals. So 'hiredate' isn't the hiredate column, it's just a varchar, which, of course, doesn't fit the date format you're specifying. Just drop the quotes and you should be fine:

SELECT hiredate
FROM   admin_emp
WHERE  TO_DATE(hiredate,'yyyy-mm-dd') >= -- No quotes 
       TO_DATE('2012-05-12','yyyy-mm-dd');

Comments

[Suganthan Madhavan Pillai]

Sorry... a different error.. ORA-01840: input value not long enough for date format , let me try

[Adrian Shum]

some rows are having only a 2 for hiredate. How does it supposed to be interpreted? Strongly suggest you redesign your table, and use DATE as type of hiredate. Going to save you lots of trouble

[Mureinik]

Your last insert statement, insert into admin_emp(empno,hiredate,index_num) values (7,'2',2); has an illegal value in hiredate - you should remove it. BTW, defining this column as date upfront would probably be a better idea and saved all this hassle.

[Rob Wise]

I agree with the advice that if you are trying to store dates, you should make that column a DATE datatype for exactly this reason (enforcing proper data entry).

[Suganthan Madhavan Pillai]

@Adrian Shum, hi I aggree... but its huge db already build contains enough data... I planning for function based index

[wallyk]

If hiredate is a date datatype, then conversion (with TO_DATE) should not be needed: WHERE hiredate >= TO_DATE('2012-05-12','yyyy-mm-dd')

[Suganthan Madhavan Pillai]

@ wallyk, Yes.. I understand... but because of million of data.. I can't easily change that.. I think I can go with function-based index

[Adrian Shum]

@Suganthan Amount of data is not an issue in fact. Simply add a new column, do one update, and drop the existing column (and probably rename the new column to old name). Whether it is millions of rows or only hundreds, it doesn't differ much. You are going to have lots of trouble even with function-based index, given your insert data can be that irrational

[Suganthan Madhavan Pillai]

@ Adrian Shum.. I understand... but in the question... I 've given only a test data... Originally it 'll be huge migration.. any way I 'll confront all urs suggestions to my team