View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
A.
WHERE order_date > TO_DATE(‘JUL 10 2006′,’MON DD YYYY’) B. WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’
B.
WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)
C.
WHERE order_date IN ( TO_DATE(‘Oct 21 2003′,’Mon DD YYYY’), TO_CHAR(‘NOV 21 2003′,’Mon DD YYYY’) )
B (WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’))
I do not think the answer is correct. You can not compare a date to a string
this B is not correct, in this question is one more answer
– WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’
and it is correct.
There are two ‘B’ answers which is wrong….
The first ‘B’ answer “WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003′” – IS CORRECT – this is comparing two strings in Date format.
The second ‘B’ answer “WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)” – IS NOT CORRECT – this is comparing a Date against a String in Date format (the operator > would not know how to interpret the Date string).
select TO_CHAR(sysdate, ‘MON DD YYYY’) from dual;
select TO_DATE(‘JUL 10 2006′,’MON DD YYYY’) from dual;
select nullif(1,2) from dual;
select nullif(1,null) from dual;
select nullif(null,1) from dual;
select nullif(”,’a’) from dual;
select nullif(”,”) from dual;
select nullif(‘a’,”) from dual;
select nullif(‘a’,’a’) from dual;
Second B is correct:
alter session set nls_date_format=’MON DD YYYY’;
select empno, ename, job, hiredate,sal from emp where hiredate > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’);
EMPNO ENAME JOB HIREDATE SAL
———- ———- ——— ———– ———-
7839 KING PRESIDENT NOV 17 2014 5000
7876 ADAMS CLERK MAY 23 2014 1100
You need to rememeber about
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm
“Implicit Data Conversion”
SQL command:
SELECT *
FROM ORDERS
WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’);
Resault:
ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 – “a non-numeric character was found where a numeric was expected”
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
AB