SQL Developer returning different date than SQL Plus command line

Question

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?

You are relying on the evil implicit data type conversion and most probably your error is caused by different NLS settings between SQL Developer and sqlplus — user330315
– user330315, Commented Oct 11, 2018 at 19:36
Is there a way to set the date dynamically without relying on type conversion? — wubbalubba
– wubbalubba, Commented Oct 11, 2018 at 19:38
yesterday and tz_yesterday are not strings, use proper data types. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 11, 2018 at 19:48
your query is working fine in oracle 12c using sql developer — kanagaraj
– kanagaraj, Commented Oct 11, 2018 at 19:55

user330315 · Accepted Answer · 2018-10-11 19:44:02Z

5

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

answered Oct 11, 2018 at 19:44

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Developer returning different date than SQL Plus command line

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related