orcale 日期显示格式化

SQL> select *

  2  from emp

  3  where hiredate='1987-11-17';

where hiredate='1987-11-17'

               *

第 3 行出现错误:

ORA-01861: 文字与格式字符串不匹配

SQL> --查看当前日期格式

SQL> select sysdate from dual;

SYSDATE

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

15-8月 -11

已选择 1 行。

SQL> --查询系统设置

SQL> select * from v$nls_parameters;

SQL> col value for a30

SQL> /

PARAMETER                                                        VALUE

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

NLS_LANGUAGE                                                     SIMPLIFIED CHINESE

NLS_TERRITORY                                                    CHINA

NLS_CURRENCY                                                     ￥

NLS_ISO_CURRENCY                                                 CHINA

NLS_NUMERIC_CHARACTERS                                           .,

NLS_CALENDAR                                                     GREGORIAN

NLS_DATE_FORMAT                                                  DD-MON-RR

NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE

NLS_CHARACTERSET                                                 ZHS16GBK

NLS_SORT                                                         BINARY

NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY                                                ￥

NLS_NCHAR_CHARACTERSET                                           AL16UTF16

NLS_COMP                                                         BINARY

PARAMETER                                                        VALUE

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

NLS_LENGTH_SEMANTICS                                             BYTE

NLS_NCHAR_CONV_EXCP                                              FALSE

已选择19行。

SQL> --修改日期格式

SQL> alter session set nls_date_format='yyyy-mm-dd';