在 oracle 过程中将字符串转换为日期时出现 Oracle 01830 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18527545/
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 01830 error while converting string to date in oracle procedure
提问by Salik
Following is the first few lines of my stored procedure in oracle. All I am trying to do is to pass a string into procedure, convert it into date and use it in a query. But it doesn't seem to work. Currently, the error I am being thrown at is as following:
以下是我在 oracle 中的存储过程的前几行。我想要做的就是将字符串传递给过程，将其转换为日期并在查询中使用它。但它似乎不起作用。目前，我被抛出的错误如下：
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "APPS.PORDUCTPLANNINGFORECAST", line 26
ORA-06512: at line 1
ORA-01830: 日期格式图片在转换整个输入字符串之前结束
ORA-06512：在“APPS.PORDUCTPLANNINGFORECAST”，第 26 行
ORA-06512：在第 1 行
CREATE OR REPLACE PROCEDURE APPS.PorductPlanningForecast (
vDateFrom IN varchar2,
vDateTo IN varchar2 ,
vForecastSetDPL2 IN varchar2,
out SYS_REFCURSOR
)
IS
L_CURSOR SYS_REFCURSOR;
vfrom date;
vto date;
BEGIN
vfrom:= TO_DATE(vDateFrom,'DD/MM/YYYY HH24:MI:SS');
vto:=TO_DATE(vDateTo,'DD/MM/YYYY HH24:MI:SS');
the lines having TO_DATE()are line 26 and 27. Also, the format I am passing in is through c# which is System.DateTimeformat("01/08/2013 12:00:00 AM") converted into a string and then passed through addparameter as gave up on passing date as date due to date conversion errors. Please help..
具有的行TO_DATE()是第 26 行和第 27 行。此外，我传入的格式是通过 c# 将System.DateTimeformat( "01/08/2013 12:00:00 AM") 转换为字符串，然后通过add参数传递，因为由于日期转换错误而放弃将日期作为日期传递。请帮忙..
采纳答案by Nick Krasnov
If you are passing in a string of the following format 01/08/2013 12:00:00 AMthen in order to successfully convert that string into a date datatype you should use the following format mask 'dd/mm/yyyy hh:mi:ss AM'which includes meridian indicator:
如果您传入以下格式的字符串，01/08/2013 12:00:00 AM那么为了成功将该字符串转换为日期数据类型，您应该使用以下格式掩码'dd/mm/yyyy hh:mi:ss AM'，其中包括子午线指示器：
to_date(vDateFrom, 'dd/mm/yyyy hh:mi:ss AM')
but how do i make it variable?it could be PM too
但我如何使它可变？它也可能是 PM
Meridian indicators are interchangeable. For both strings 01/08/2013 2:00:00 AMand 01/08/2013 2:00:00 PMfor instance, you can use date format model with one of the meridian indicators, whether it AMor PM. Here is an example:
子午线指标可以互换。例如，对于字符串01/08/2013 2:00:00 AM和01/08/2013 2:00:00 PM示例，您可以将日期格式模型与子午线指标之一一起使用，无论是它AM还是PM. 下面是一个例子：
select to_date('01/08/2013 2:00:00 AM', 'dd/mm/yyyy hh:mi:ss AM') as res
from dual
Result:
结果：
Res
-----------
01.08.2013 2:00:00
select to_date('01/08/2013 2:00:00 PM', 'dd/mm/yyyy hh:mi:ss AM') as res
from dual
Res
-----------------
01.08.2013 14:00:00
