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
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.