This SQL working fine with SQLPLUS but not working in SQL Developer, giving an error ORA-01830: date format picture ends before converting entire input string
Which version of SQL Developer? It works fine on 4.0.3 connected to 11.2 XE.
Duplicate Thread and wrong forum!
Please do NOT post duplicate threads.
Mark this new thread ANSWERED and if you still need help repost your question in the Sql and PL/SQL forum:
The actual issue is with your code and NLS settings, not SQL Developer.
What are you trying to achieve with "TO_DATE (SYSDATE - 1, 'DD-Mon-YY')" part? SYSDATE is already DATE type, as well as "SYSDATE-1". So what you are doing here is implicitly converting "SYSDATE-1" from date to varchar2 using your session's NLS_DATE_FORMAT parameter, then explicitly converting it back to date using 'DD-Mon-YY' format.
You can check your NLS settings by executing
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'
Unless you get "DD-Mon-YY" as a result, your code will never work.
If you execute
alter session set NLS_DATE_FORMAT = 'DD-Mon-YY';
before your query in the SQL Developer, everything will "work".
You should NOT, however, rely on implicit conversion and NLS settings, it's a horrible practice. That your code works in SQL*Plus is an accident. Rewrite it.
You can define NLS settings SQLDeveloper sets on logon for every session in Preferences-Database-NLS, in "Date Format" fields.