sql not a valid month error in sql command line

Question

Actually I was trying to get all columns within a date range in Oracle db and I am successful in doing so in SQL Developer but when I execute the same code in SQL command line interface I get an error

ORA-01843 : not a valid month

I know its an error which comes in to_date format but it gets executed in SQL developer as exactly I expected but not in SQL command line. Why does this happen??

select to_date('01/04/2020') from dual;

Even this command is not working.

I had tried all format methods for the to_date method conversion and I checked nls_date_format which shows 'DD-MM-RR'

thanks i didnt put a command to alter the session, but my problem was the query which i build was working as exactly as i wanted in sql developer but it was not working in sql command line, so when i added a query to alter the session in sql command line it worked fine — amjaad muhammed
– amjaad muhammed, Commented Apr 25, 2020 at 20:58

Littlefoot · Accepted Answer · 2020-04-21 06:07:07Z

0

I'd suggest you to always provide appropriate format mask. Don't rely on Oracle implicitly converting & guessing what you wanted to do.

String that looks like '01/04/20' could be anything, depending on format you used (dd/mm/yy, yy/dd/mm, mm/yy/dd ... all of that is valid, but only one is correct).

SQL> alter session set nls_Date_format = 'dd-mm-rr';

Session altered.

SQL> select to_date('01/04/2020', 'dd/mm/yyyy') result from dual;

RESULT
------------------------------
01-04-20

SQL>

answered Apr 21, 2020 at 6:07

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

sql not a valid month error in sql 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