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';

 

转载于:https://www.cnblogs.com/chengbao/p/5379862.html