CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldevelopersqlplus
wubbalubba
wubbalubba

Reputation: 764

SQL Developer returning different date than SQL Plus command line

I have the following script in SQL Developer, but it seems to be returning an invalid date when it is run in SQL Plus command line.

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;

This is the output given by SQL Developer, which is correct:

10-OCT-18
10-OCT-18 12.00.00.000000 AM

This is what I get in 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

Can someone explain the discrepancy here?

Upvotes: 0

Views: 308

Answers (1)

user330315
user330315

Reputation:

Don't store date or timestamp values in varchar variables, always use the correct data types:

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;
/

Upvotes: 5

Related Questions