NLS_DATE_FORMAT 변경하기

출처: https://m.blog.naver.com/hanajava/220708733692

현재 날짜 조회

 

SQL> select sysdate from dual; SYSDATE -------- 16/05/13 SQL> select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2016/05/13 15:10:51 SQL> select * from v$nls_parameters; PARAMETER                                                        VALUE ---------------------------------------------------------------- ------------------------------- NLS_LANGUAGE                                                     KOREAN NLS_TERRITORY                                                    KOREA NLS_CURRENCY                                                     ￦ NLS_ISO_CURRENCY                                                 KOREA NLS_NUMERIC_CHARACTERS                                           ., NLS_CALENDAR                                                     GREGORIAN NLS_DATE_FORMAT                                                  RR/MM/DD NLS_DATE_LANGUAGE                                                KOREAN NLS_CHARACTERSET                                                 KO16MSWIN949 NLS_SORT                                                         BINARY NLS_TIME_FORMAT                                                  HH24:MI:SSXFF PARAMETER                                                        VALUE ---------------------------------------------------------------- ------------------------------- NLS_TIMESTAMP_FORMAT                                             RR/MM/DD HH24:MI:SSXFF NLS_TIME_TZ_FORMAT                                               HH24:MI:SSXFF TZR NLS_TIMESTAMP_TZ_FORMAT                                          RR/MM/DD HH24:MI:SSXFF TZR NLS_DUAL_CURRENCY                                                ￦ NLS_NCHAR_CHARACTERSET                                           AL16UTF16 NLS_COMP                                                         BINARY NLS_LENGTH_SEMANTICS                                             BYTE NLS_NCHAR_CONV_EXCP                                              FALSE 19 개의 행이 선택되었습니다. SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS'; 세션이 변경되었습니다. SQL> select sysdate from dual; SYSDATE ------------------- 2016/05/13 15:13:20 SQL> SELECT sysdate from dual; SYSDATE ------------ 19-MAR-16 SQL> SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2016-05-19 16:24:50​

 

 

NLS_PARAMETER 값 조회 

 

SQL> select * from v$NLS_PARAMETERS ;

 

 

 

DATE 형식 수정 

 

ALTER session SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ;ALTER session SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS' ;

 

 

 

 

[ORACLE GUIDE 발췌]

Working in a Global Environment(v$NLS_PARAMETERS)

-> 해석하자면, 오직 ALTER SESSION으로 값을 변경할 수 있고 disconnecting and reconnecting 할 경우 기본 세팅 값으로 revert(되돌아감) 됨.

-> 즉, 영구적으로 변경은 불가 ALTER SYSTEM SET 하면 적용이 된 것처럼 작용하지만 실제로 원래 값으로 되돌아갑니다.

-> 관리자나 유저 등 개별적으로 비영구 변경이 가능