[Oracle] 날짜 형식 변경 (NLS_DATE_FORMAT)

기존

SQL> select sysdate from dual;

SYSDATE
--------
21/12/24

 

현재 파라미터 확인

SQL> select * from v$nls_parameters;

PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
NLS_LANGUAGE                   AMERICAN                                0
NLS_TERRITORY                  KOREA                                   0
NLS_CURRENCY                   ￦                                      0
NLS_ISO_CURRENCY               KOREA                                   0
NLS_NUMERIC_CHARACTERS         .,                                      0
NLS_CALENDAR                   GREGORIAN                               0
NLS_DATE_FORMAT                RR/MM/DD                                0
NLS_DATE_LANGUAGE              AMERICAN                                0
NLS_CHARACTERSET               AL32UTF8                                0
NLS_SORT                       BINARY                                  0
NLS_TIME_FORMAT                HH24:MI:SSXFF                           0
NLS_TIMESTAMP_FORMAT           RR/MM/DD HH24:MI:SSXFF                  0
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR                       0
NLS_TIMESTAMP_TZ_FORMAT        RR/MM/DD HH24:MI:SSXFF TZR              0
NLS_DUAL_CURRENCY              ￦                                      0
NLS_NCHAR_CHARACTERSET         AL16UTF16                               0
NLS_COMP                       BINARY                                  0
NLS_LENGTH_SEMANTICS           BYTE                                    0
NLS_NCHAR_CONV_EXCP            FALSE                                   0

19 rows selected.

 

ALTER 명령으로 nls_date_format 변경 및 확인

SQL> alter session set nls_date_format='YYYYMMDD-HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------
20211224-10:10:23

 

참고

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm#REFRN10119

NLS_DATE_FORMAT