Oracle:年必须在-4713和+9999之间,并且不能为0 [英] Oracle: year must be between -4713 and +9999, and not be 0
本文介绍了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');
这篇关于Oracle:年必须在-4713和+9999之间,并且不能为0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文