oracle ORA-01861 文字与 SELECT 语句上的格式字符串不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15103230/
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
ORA-01861 literal does not match format string on SELECT statement
提问by Oneb
Good day.
再会。
I am executing a query and encountering:
我正在执行查询并遇到：
ORA-01861 literal does not match format string error.
I executed this query and IT WORKED.
我执行了这个查询并且它起作用了。
SELECT * FROM GCACC_OPERATION_DETAIL WHERE id_notice in (75078741)
AND id_analytical_center in (100000002)
AND interface_date = '2013-06-30'
AND generic_client = 'someGenClient'
AND document_class = 'DOCCLA0001'
AND accounting_tag_identifier = 1
AND generated_actual_acc_doc
IN (select id_accounting_document
from gcacc_accounting_document
where document_status = 'DOCSTA0001');
My other query is written below which DID NOT WORK.
我的另一个查询写在下面，它不起作用。
SELECT * FROM GCACC_OPERATION_DETAIL WHERE id_notice IN (75078741)
AND id_analytical_center in (100000002)
AND generic_client = 'someGenClient'
AND document_class = 'DOCCLA0001'
AND accounting_tag_identifier = 1
AND interface_date = '2013-06-30'
AND ind_pending_process = 1
AND operation_type
IN (select cod_develop from gcacc_operation_type where ind_operation = 'B');
This is really weird because the error happens in the date part but I am writing the same syntax for the date part. I maybe missing something silly here and fresher eyes are needed. Thanks in advance!
这真的很奇怪，因为错误发生在日期部分，但我正在为日期部分编写相同的语法。我可能在这里错过了一些愚蠢的东西，需要新鲜的眼睛。提前致谢！
回答by OldProgrammer
I don't know what the specific problem is, but assuming "interface_date" is a DATE type, it is bad practice to use literals in a query for a date. This makes the assumption that the default NLS_DATE_FORMAT agrees with your date literal. That will come back to bite you. To ensure that your date constraint is portable, change to this:
我不知道具体问题是什么，但假设“interface_date”是 DATE 类型，在日期查询中使用文字是不好的做法。这假设默认 NLS_DATE_FORMAT 与您的日期文字一致。那会回来咬你的。为确保您的日期约束是可移植的，请更改为：
AND interface_date = to_date('2013-06-30','YYYY-MM-DD')
