OCP-1Z0-051 第91题 TO_DATE转换函数

一、原题

View the Exhibit and examine the structure of the PROMOTIONS table.

Which two SQL statements would execute successfully? (Choose two.)
A. UPDATE promotions
            SET promo_cost = promo_cost+ 100
     WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';

B. SELECT promo_begin_date
        FROM promotions
     WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';

C. UPDATE promotions
            SET promo_cost = promo_cost+ 100
     WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));

D. SELECT TO_CHAR(promo_begin_date,'dd/month')
        FROM promotions
     WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));

答案：AB

二、题目翻译

下面是PROMOTIONS表的结构
哪两个SQL语句能执行成功？（选择两个）

三、题目解析

C选项不正确，SUBSTR的结果是字符串'2000',TO_DATE需要第二个参数，日期格式，这里使用YYYY格式符才能转换。
D选项不正确，TO_DATE函数，不写第二个参数，是转成默认日期格式，但这里的字符串显然不是默认日期格式，这样写是无法进行转换的，需要指定日期格式。

SQL> select TO_DATE('JUN 01 98') from dual;
select TO_DATE('JUN 01 98') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL> select TO_DATE('JUN 01 98','mm-dd-yyyy') from dual;

TO_DATE('JUN
------------
01-JUN-98