OCP-047-276 TO_DATE

276

A

SQL> select to_date('JUL 10 2006','MON DD YYYY') NEWDATE FROM DUAL;

NEWDATE
------------
10-JUL-06

有显示，但与格式不同，函数中的格式指的是函数中CHAR对应的格式。

B

SQL> select order_date, TO_CHAR(order_date, 'MON DD YYYY') NEWCHAR
  2    from orders
  3   WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'MAR 21 2004';

ORDER_DATE                                                                  NEWCHAR
--------------------------------------------------------------------------- --------------
21-MAR-04 06.18.21.862632 AM                                                MAR 21 2004

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

C char 与 date 不能比较，这里演示函数结果。

SQL> select order_date,
  2         TO_CHAR(SYSDATE, 'MON DD YYYY'),
  3         TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
  4    from orders
  5  where rownum <10;

ORDER_DATE                                                                  TO_CHAR(SYSDAT TO_CHAR(ADD_MO
--------------------------------------------------------------------------- -------------- --------------
21-MAR-04 06.18.21.862632 AM                                                OCT 19 2013    APR 19 2014
09-JAN-06 10.19.44.123456 AM                                                OCT 19 2013    APR 19 2014
09-JAN-06 11.34.13.112233 AM                                                OCT 19 2013    APR 19 2014
26-JAN-06 11.22.51.962632 PM                                                OCT 19 2013    APR 19 2014
03-FEB-06 10.19.11.227550 AM                                                OCT 19 2013    APR 19 2014
28-FEB-06 07.03.03.828330 AM                                                OCT 19 2013    APR 19 2014
30-MAR-06 12.22.09.509801 AM                                                OCT 19 2013    APR 19 2014
30-MAR-06 03.34.50.545196 AM                                                OCT 19 2013    APR 19 2014
28-JUL-06 12.22.59.662632 AM                                                OCT 19 2013    APR 19 2014

9 rows selected.

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

D char 与 date 不能比较

SQL> select * from orders WHERE order_date  IN  ( TO_DATE('Oct 21 2003','Mon DD YYYY'), TO_CHAR('NOV 21 2003','Mon DD YYYY') ) ;
select * from orders WHERE order_date  IN  ( TO_DATE('Oct 21 2003','Mon DD YYYY'), TO_CHAR('NOV 21 2003','Mon DD YYYY') )
                                                                                           *
ERROR at line 1:
ORA-01722: invalid number