CODEWITHSUNDEEP
sqloracle-databasedateselect
Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

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

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);

Upvotes: 6

Views: 23272

Answers (2)

Mauricio Cruz
Mauricio Cruz

Reputation: 1

Use 'rrrr' format for year:

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

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');

Upvotes: 5

Related Questions