OCP-1Z0-新051-61题版本-39

QUESTION NO: 39

You need to calculate the number of days from 1st Jan 2007 till date:

Dates are stored in the default format of dd-mm-rr.

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

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

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

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

D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL

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

Answer: A,C

答案解析：

参考：88：http://blog.csdn.net/rlhua/article/details/12859371

hr@TESTDB>SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;

SYSDATE-TO_DATE('01/JANUARY/2007')

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

                        2553.97258

hr@TESTDB>SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;

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

                                        *

ERROR at line 1:

ORA-01722: invalid number

hr@TESTDB>SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;

SYSDATE-TO_DATE('01-JANUARY-2007')

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

                        2553.97277

hr@TESTDB>SELECT SYSDATE - '01-JAN-2007' FROM DUAL;

SELECT SYSDATE - '01-JAN-2007' FROM DUAL

                 *

ERROR at line 1:

ORA-01722: invalid number

hr@TESTDB>SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

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

       *

ERROR at line 1:

ORA-01722: invalid number

Explanation: