SQL Developer返回的日期与SQL Plus命令行不同
SQL Developer返回的日期与SQL Plus命令行不同
提问于 2018-10-12 03:33:42
我在SQL Developer中有以下脚本，但在SQL Plus命令行中运行时，它似乎返回了无效的日期。
set serveroutput on;
DECLARE
yesterday varchar2(30);
tz_yesterday varchar2(30);
BEGIN
select sysdate - 1 into yesterday from dual;
select cast(yesterday as timestamp WITH LOCAL TIME ZONE) into tz_yesterday from dual;
dbms_output.put_line(yesterday);
dbms_output.put_line(tz_yesterday);
END;这是SQL Developer给出的输出，它是正确的：
10-OCT-18
10-OCT-18 12.00.00.000000 AM这是我在SQL Plus中得到的结果
SQL> l
1 DECLARE
2 g_vendor_id NUMBER;
3 g_product_id NUMBER;
4 yesterday varchar2(30);
5 tz_yesterday varchar2(30);
6 g_user_id NUMBER;
7 BEGIN
8 select sysdate - 1 into yesterday from dual;
9 select cast(yesterday as timestamp WITH LOCAL TIME ZONE) into tz_yesterday from dual;
10 dbms_output.put_line(yesterday);
11 dbms_output.put_line(tz_yesterday);
12* End;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 9有人能解释一下这里的差异吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-10-12 03:44:03
不要将日期或时间戳值存储在varchar变量中，始终使用正确的数据类型：
set serveroutput on;
DECLARE
yesterday date;
tz_yesterday timestamp with local time zone;
BEGIN
yesterday := sysdate - 1;
tz_yesterday := cast(yesterday as timestamp WITH LOCAL TIME ZONE);
dbms_output.put_line(yesterday);
dbms_output.put_line(tz_yesterday);
END;
/页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52767637
复制相关文章
相似问题