OCP 1Z0 051 88

来源：互联网发布：ubuntu终端显示中文编辑：程序博客网时间：2024/05/20 17:08

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;

日期加减时，需要显示转换

SQL> alter session set nls_date_language='american';Session alteredSQL> alter session set nls_date_format='dd-mon-rr';Session alteredSQL> SELECT SYSDATE - '01-JAN-2007' FROM DUAL; SELECT SYSDATE - '01-JAN-2007' FROM DUALORA-01722: 无效数字

SQL> SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;SYSDATE-TO_DATE('01/JANUARY/20------------------------------              2698.900081018521 row selectedSQL> SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;SYSDATE-TO_DATE('01-JANUARY-20------------------------------              2698.900081018521 row selected

字符串不能作减法 D错

SQL>  SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL; SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUALORA-01722: 无效数字

TO_DATE(SYSDATE, 'DD/MONTH/YYYY') 没问题，但转换后还是与A一样。E错

SQL> SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') FROM DUAL;TO_DATE(SYSDATE,'DD/MONTH/YYYY------------------------------0014-5-221 row selectedSQL> SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUALORA-01722: 无效数字

Answer: BC

0 0