简单的 Oracle 查询：文字与格式字符串不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4884071/
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
Simple Oracle query: literal does not match format string
提问by Kasper Hansen
I want to execute a simple function in Oracle. The signature is defined as follows:
我想在 Oracle 中执行一个简单的函数。签名定义如下：
CREATE OR REPLACE FUNCTION NewCaseListForValidation
(
p_fromDate in DATE,
p_toDate in DATE,
p_rowCount in INT
)
RETURN
SYS_REFCURSOR
IS
return_value SYS_REFCURSOR;
...
I should be able to execute it with:
我应该能够执行它：
var rc refcursor
exec :rc := newcaselistforvalidation('2010-01-01','2011-01-01',100);
print :rc
But when typing "newcaselistforvalidation('2010-01-01','2011-01-01',100)", I get:
但是当输入“newcaselistforvalidation('2010-01-01','2011-01-01',100)”时，我得到：
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at line 1
I googled a bit and it seems I can't figure out to type the date in a correct format. Can anyone help me?
我用谷歌搜索了一下，似乎我不知道以正确的格式输入日期。谁能帮我？
回答by diagonalbatman
Query NLS_PARAMETERS in Oracle- you will then be able to see what format your DB is accepting dates in.
在 Oracle 中查询 NLS_PARAMETERS - 然后您将能够看到您的数据库接受的日期格式。
Typically however i use the to_date() function:
但是通常我使用 to_date() 函数：
to_date('01-01-2011','DD-MM-YYYY');
In the UK to input my dates.
在英国输入我的日期。
回答by a_horse_with_no_name
An alternative to the to_date() function is to use the ANSI standard format for DATE or TIMESTAMP literals:
to_date() 函数的替代方法是对 DATE 或 TIMESTAMP 文字使用 ANSI 标准格式：
DATE '2010-01-31' TIMESTAMP '2010-01-31 21:22:23'
Date and time is always specified using ISO rules (YYYY-MM-DD and 24hour format for time)
日期和时间总是使用 ISO 规则指定（YYYY-MM-DD 和 24 小时格式的时间）
This also works on a lot of other (standard compliant) DBMS.
这也适用于许多其他（符合标准的）DBMS。
回答by dovka
It's best not to rely on particular value in NLS_PARAMETERSsettings, cause this will make your function breakin the env with another NLS_DATE_FORMAT.
I'd explicitly specify date formattingin your function as suggested in the answer above
最好不要依赖NLS_PARAMETERS设置中的特定值，因为这会使您的函数在 env 中与另一个NLS_DATE_FORMAT中断。
我会按照上面的答案中的建议在您的函数中明确指定日期格式
exec :rc := newcaselistforvalidation(to_date('2010-01-01','YYYY-MM-DD'),to_date('2011-01-01','YYYY-MM-DD'),100);
回答by Jared
INSERT INTO tblDate (dateStart) Values ('20-JUN-2013'); If you change month integer into a string 'DD-MON-YYYY' works as a valid data string without having to preface it with the DATE identifier.
INSERT INTO tblDate (dateStart) 值 ('20-JUN-2013'); 如果您将月份整数更改为字符串 'DD-MON-YYYY' 将用作有效的数据字符串，而无需以 DATE 标识符开头。
