Oracle SQL - DATE 大于语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30555790/
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 SQL - DATE greater than statement
提问by user3521826
As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query.
正如标题所说，我想找到一种方法来通过查询检查我的哪些数据集是从 SYSDATE 开始的过去 6 个月。
SELECT * FROM OrderArchive
WHERE OrderDate <= '31 Dec 2014';
I've tried the following but it returns an error saying my date format is wrong. However, inserting the data I used that date format as requested/intended and had no issues.
我尝试了以下操作，但它返回一个错误，指出我的日期格式错误。但是，插入数据时我按照要求/预期使用了该日期格式，并且没有任何问题。
Error at Command Line : 10 Column : 25
Blockquote
Error report -
SQL Error: ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.
*Action: Correct the format string to match the literal.
命令行错误：10 列：25
块引用
错误报告 -
SQL 错误：ORA-01861：文字与格式字符串 01861 不匹配。00000 -“文字与格式字符串不匹配”
*原因：输入中的文字必须与格式字符串中的文字长度相同（前导空格除外）。如果已打开“FX”修饰符，则文字必须完全匹配，没有多余的空格。
*操作：更正格式字符串以匹配文字。
回答by Sylvain Leroux
As your query string is a literal, and assuming your dates are properly stored as DATEyou should use date literals:
由于您的查询字符串是文字，并且假设您的日期正确存储，因为DATE您应该使用日期文字：
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_LANGUAGEparameter as you are using US abbreviated month names. That way, it won't break on some localized Oracle Installation:
如果您想使用TO_DATE（例如，因为您的查询值不是文字），我建议您在使用美国缩写月份名称时显式设置NLS_DATE_LANGUAGE参数。这样，它就不会在某些本地化的 Oracle 安装中中断：
SELECT * FROM OrderArchive
WHERE OrderDate <= to_date('31 Dec 2014', 'DD MON YYYY',
'NLS_DATE_LANGUAGE = American');
回答by Sachu
You need to convert the string to date using the to_date()function
您需要使用该to_date()函数将字符串转换为日期
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 OrderDateis stored in Date format. If it is Varcharyou should apply to_date()func on that column also like
这仅在OrderDate存储在Date format. 如果是，Varchar你应该to_date()在该列上应用func 也喜欢
SELECT * FROM OrderArchive
WHERE to_date(OrderDate,'yyyy-Mm-dd') <= to_date('2014-12-31','yyyy-MM-dd');
回答by kamokaze
you have to use the To_Date() function to convert the string to date ! http://www.techonthenet.com/oracle/functions/to_date.php
您必须使用 To_Date() 函数将字符串转换为日期！ http://www.techonthenet.com/oracle/functions/to_date.php
