（88）You need to calculate the number of days from 1st January 2007 till date. Dates are stored in the default format of dd-mon-rr.

Which SQL statements would give the required output? (Choose two .)

A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL;

B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;

C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;

D. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;

E. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;

答案：（B、C）

解析：

--A（X）,会隐式将'11-MAR-14'转换为数字,而不是日期。

scott@TESTDB11>select sysdate - '11-MAR-14' from dual;

select sysdate - '11-MAR-14' from dual

*

ERROR at line 1:

ORA-01722: invalid number

-- B, 与默认的日期格式一致（即使指定4位的年也可以)

scott@TESTDB11>select sysdate - to_date('11-MAR-14') from dual;

SYSDATE-TO_DATE('11-MAR-14')

----------------------------

-.76417824

-- C, 与默认的日期格式的区别只在分隔符上,也可以正确执行

scott@TESTDB11>select sysdate - to_date('11/MAR/14') from dual;

SYSDATE-TO_DATE('11/MAR/14')

----------------------------

-.76405093

ITPub博客

OCP 1Z0-051-88