Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ece Nil.

Asked: February 11, 2017 - 5:03 pm UTC

Last updated: February 13, 2017 - 5:23 am UTC

Version: Oracle Database 11g Express Edition

Viewed 1000+ times

You Asked

I'm using Apple's macbook pro and I downloaded Virtualbox in order to work with Oracle 11g. I installed Windows 7 in Virtualbox. I wrote my queries in sql*plus. I have a problem with single quotes. Even the query is work in the classroom, it always gives an error in my laptop. Also I tried copy and paste the query but it doesn't work because of the single quote. My query is:

SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN '01-JAN-1981' AND '31-DEC-1981';

Unfortunately sql*plus gives ORA-01858 in the first single quote. How can I fix the bug? May I solve the problem with set NLS_NUMERIC_CHARACTERS and how?

With my best regards

and Connor said...

Well, ideally you always use date datatypes, eg

SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN date '1981-01-01' and '1981-12-31'


or

SELECT ename, hiredate
FROM emp
WHERE hiredate BETWEEN to_date('01-JAN-1981','dd-mon-yyyy') AND to_date('31-DEC-1981','dd-mon-yyyy');


but our automatic conversion will be based on NLS_DATE_FORMAT

SQL> alter session set nls_date_format = 'DD/MM/YYYY';

Session altered.

SQL> select to_date('01/02/1999') from dual;

TO_DATE('0
----------
01/02/1999

1 row selected.

SQL> alter session set nls_date_format = 'YYYY-MON-DD';

Session altered.

SQL> select to_date('01/02/1999') from dual;
select to_date('01/02/1999') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('1999-JAN-13') from dual;

TO_DATE('19
-----------
1999-JAN-13

1 row selected.



Is this answer out of date? If it is, please let us know via a Comment