SQL 转换整个输入字符串错误之前日期格式图片结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24950858/
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
date format picture ends before converting entire input string error
提问by user3868442
I have this procedure:
我有这个程序：
create or replace Procedure return_rows_LECTURE_BY_DATE (in_date in date, out_cursor OUT SYS_REFCURSOR) As
Begin
OPEN out_cursor for
select *
FROM COURSE_LECTURE
WHERE LECT_DATE_TIME_START >= to_timestamp(in_date, 'dd-mm-yyyy')
and LECT_DATE_TIME_START < to_timestamp(in_date+1, 'dd-mm-yyyy')
ORDER BY LECT_DATE_TIME_START;
End;
input: date, output: lectures on this date. The dates in the table (view) is TIMESTAMP.
输入：日期，输出：该日期的讲座。表（视图）中的日期是 TIMESTAMP。
I want to run this procedure. I tried this:
我想运行这个程序。我试过这个：
declare
k SYS_REFCURSOR;
--t DATE:= to_date('2010-12-14:09:56:53', 'YYYY-MM-DD:HH24:MI:SS') ;
res COURSE_LECTURE%rowtype;
begin
return_rows_LECTURE_BY_DATE(to_date('2010-12-14', 'YYYY-MM-DD'),k);
loop
FETCH k into res;
Exit when k%notFound;
DBMS_OUTPUT.PUT_LINE(res.COURSE_NAME );
end loop;
end;
But I got this error:
但我收到了这个错误：
Error report - ORA-01830: date format picture ends before converting entire input string ORA-06512: at "HR.RETURN_ROWS_LECTURE_BY_DATE", line 4 ORA-06512: at line 6 01830. 00000 - "date format picture ends before converting entire input string"
错误报告 - ORA-01830：日期格式图片在转换整个输入字符串之前结束 ORA-06512：在“HR.RETURN_ROWS_LECTURE_BY_DATE”，第 4 行 ORA-06512：在第 6 行 01830. 00000 -“日期格式图片在转换整个输入字符串之前结束”
回答by paubo147
You are converting the date into a timestamp, by using TO_TIMESTAMP(), which takes a character as a parameter. You should use CAST()instead, which converts one datatype to another; for instance:
您正在通过使用将日期转换为时间戳，TO_TIMESTAMP()它将字符作为参数。您应该CAST()改用，它将一种数据类型转换为另一种数据类型；例如：
WHERE LECT_DATE_TIME_START >= CAST(in_date AS TIMESTAMP)
You should be doing this with all of your conversions from dates to timestamps; so to_timestamp(in_date+1, 'dd-mm-yyyy')becomes CAST((in_date + 1) AS TIMESTAMP).
您应该对从日期到时间戳的所有转换进行此操作；所以to_timestamp(in_date+1, 'dd-mm-yyyy')就变成了CAST((in_date + 1) AS TIMESTAMP)。
回答by Maksim Sirotkin
The problem is with statement to_timestamp(in_date, 'dd-mm-yyyy') the format provided is too short you can use it without any format condition to_timestamp(in_date).
问题在于语句 to_timestamp(in_date, 'dd-mm-yyyy') 提供的格式太短，您可以在没有任何格式条件 to_timestamp(in_date) 的情况下使用它。
