Oracle：年份必须介于 -4713 和 +9999 之间，且不能为 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27597788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: year must be between -4713 and +9999, and not be 0
提问by Suganthan Madhavan Pillai
I have an Oracle table like this
我有一个像这样的 Oracle 表
|---------------------------------|
|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 queryagainst this table
我正在尝试query对此表执行此操作
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);
采纳答案by Mureinik
Single quotes (') in SQL denote string literals. So 'hiredate'isn't the hiredatecolumn, 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:
'SQL 中的单引号 ( ) 表示字符串文字。所以'hiredate'不是hiredate列，它只是一个 varchar，当然，它不适合您指定的日期格式。只需删除引号，你应该没问题：
SELECT hiredate
FROM admin_emp
WHERE TO_DATE(hiredate,'yyyy-mm-dd') >= -- No quotes
TO_DATE('2012-05-12','yyyy-mm-dd');
回答by Mauricio Cruz
Use 'rrrr' format for year:
使用 'rrrr' 格式表示年份：
SELECT hiredate
FROM admin_emp
WHERE TO_DATE(hiredate,'rrrr-mm-dd') >= TO_DATE('2012-05-12','rrrr-mm-dd');
