oracle ORA-01830: 日期格式图片在 TOAD 中转换整个输入字符串之前结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30800375/
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-01830: date format picture ends before converting entire input string in TOAD
提问by Namrata
I have a script in which I am writing functions and procedures. The script works absolutely fine in Oracle SQL developer without any errors or warnings. This script I have to provide to a client side customer. The customer user runs the same script in TOADand he gets the error message
我有一个脚本，我在其中编写函数和过程。该脚本在 Oracle SQL 开发人员中工作得非常好，没有任何错误或警告。这个脚本我必须提供给客户端客户。客户用户运行相同的脚本TOAD，他收到错误消息
ORA-01830: date format picture ends before converting entire input string
ORA-01830: date format picture ends before converting entire input string
In this situation, I really don't know how to handle such errors. Is this a bug in TOAD?
I use to_dateand to_timestampin my functions and there I get an error.
在这种情况下，我真的不知道如何处理此类错误。这是一个错误TOAD吗？我在我的函数中使用to_date和 to_timestamp，但出现错误。
回答by Alex Poole
You're either relying on implicit date conversions or implicit format models; since you said you're using to_dateand to_timestampit seems to be the latter. You haven't shown your code but the error implies you're calling those functions with a string value in the format you expect to see, but not explicitly providing the format model as the second argument to the function. That will cause the session's NLS_DATE_FORMAT setting to be used. If you do:
您要么依赖于隐式日期转换，要么依赖于隐式格式模型；既然你说你正在使用to_date和to_timestamp它似乎是后者。您还没有显示您的代码，但错误意味着您正在使用您希望看到的格式的字符串值调用这些函数，但没有明确提供格式模型作为函数的第二个参数。这将导致使用会话的 NLS_DATE_FORMAT 设置。如果你这样做：
to_date('12/06/2015 09:10:11')
then you are really effectively doing:
那么你真的很有效地在做：
to_date('12/06/2015 09:10:11', (select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'))
You cannot control how your customer will have their NLS environment configured, and so you should never rely on implicit conversions or NLS assumptions.
您无法控制您的客户如何配置他们的 NLS 环境，因此您永远不应依赖隐式转换或 NLS 假设。
As it says in the documentation:
正如文档中所说：
Caution:
It is good practice always to specify a format mask (fmt) with TO_DATE, as shown in the examples in the section that follows. When it is used without a format mask, the function is valid only if char uses the same format as is determined by the NLS_TERRITORY or NLS_DATE_FORMAT parameters. Furthermore, the function may not be stable across databases unless the explicit format mask is specified to avoid dependencies.
警告：
始终使用 TO_DATE 指定格式掩码 (fmt) 是一种很好的做法，如以下部分中的示例所示。当它在没有格式掩码的情况下使用时，只有当 char 使用由 NLS_TERRITORY 或 NLS_DATE_FORMAT 参数确定的相同格式时，该函数才有效。此外，除非指定显式格式掩码以避免依赖性，否则该函数可能在数据库之间不稳定。
You can see how this affects a simple query with this:
你可以看到这如何影响一个简单的查询：
SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> select to_date('12/06/2015 09:10:11') from dual;
TO_DATE('12/06/2015
-------------------
12/06/2015 09:10:11
SQL> alter session set nls_date_format = 'DD/MM/YYYY';
Session altered.
SQL> select to_date('12/06/2015 09:10:11') from dual;
select to_date('12/06/2015 09:10:11') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
For the same reason you should avoid month names as their interpretation depends on the NLS_DATE_LANGUAGE; though you can at least override that at query level if you really have to.
出于同样的原因，您应该避免使用月份名称，因为它们的解释取决于 NLS_DATE_LANGUAGE；尽管如果确实需要，您至少可以在查询级别覆盖它。
You might also want to consider date and timestamp literals if you're working with fixed values:
如果您使用固定值，您可能还需要考虑日期和时间戳文字：
select date '2015-06-12' ...
select timestamp '2015-06-12 09:10:11' ...
but if you're constructing the string to convert to a date then that won't be appropriate.
但如果您正在构建字符串以转换为日期，那么这将不合适。
