Oracle:年必须在

本文介绍了Oracle:年必须在-4713和+9999之间，并且不能为0的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个像这样的Oracle表

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

我正在尝试对此表执行query

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

但是出现错误

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

有什么主意吗?这是什么问题?

Any idea..? What is the issue here?

查询基础:

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

推荐答案

SQL中的单引号(')表示字符串文字.所以'hiredate'不是hiredate列，它只是一个varchar，当然，它不适合您指定的日期格式.只需删除引号就可以了:

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