Oracle SQL-DATE 大于语句

正如标题所说，我想找到一种方法，通过查询检查我的哪些数据集是过去6个月从 SYSDATE。

SELECT * FROM OrderArchive
WHERE OrderDate <= '31 Dec 2014';

我尝试了以下方法，但它返回一个错误，说我的日期格式是错误的。但是，插入数据时我使用了日期格式，没有任何问题。

命令行错误: 10列: 25

引用

错误报告-

SQL 错误: ORA-01861: 文字与格式字符串不匹配 01861.00000-“文字不匹配格式字符串”

* 原因: 输入中的文字长度必须与 格式字符串(前导空格除外) “ FX”修饰符已打开，文字必须完全匹配, 没有额外的空格。

操作: 更正格式字符串以匹配文字。

370011 次浏览
小开

you have to use the To_Date() function to convert the string to date ! http://www.techonthenet.com/oracle/functions/to_date.php

小开

As your query string is a literal, and assuming your dates are properly stored as DATE you should use date literals:

SELECT * FROM OrderArchive
WHERE OrderDate <= DATE '2015-12-31'

If you want to use TO_DATE (because, for example, your query value is not a literal), I suggest you to explicitly set the NLS_DATE_LANGUAGE parameter as you are using US abbreviated month names. That way, it won't break on some localized Oracle Installation:

SELECT * FROM OrderArchive
WHERE OrderDate <= to_date('31 Dec 2014', 'DD MON YYYY',
'NLS_DATE_LANGUAGE = American');
小开

You need to convert the string to date using the to_date() function

SELECT * FROM OrderArchive
WHERE OrderDate <= to_date('31-Dec-2014','DD-MON-YYYY');

OR 

SELECT * FROM OrderArchive
WHERE OrderDate <= to_date('31 Dec 2014','DD MON YYYY');

OR

SELECT * FROM OrderArchive
WHERE OrderDate <= to_date('2014-12-31','yyyy-MM-dd');

This will work only if OrderDate is stored in Date format. If it is Varchar you should apply to_date() func on that column also like

 SELECT * FROM OrderArchive
WHERE to_date(OrderDate,'yyyy-Mm-dd') <= to_date('2014-12-31','yyyy-MM-dd');

提交答案