꿈꾸는 개발자, DBA 커뮤니티 구루비

날짜와 관련된 유용한 함수들

TO_DATE & TO_TIMESTAMP & TO_CHAR(datetime)
ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
NEXT_DAY
ROUND
TRUNC
EXTRACT
참고

TO_DATE & TO_TIMESTAMP & TO_CHAR(datetime)

TO_DATE
{info:title=Syntax}
TO_DATE(CHAR [,FORMAT][,nlsparam])
{info}
- TO_DATE는 CHAR, VARCHAR2, NCHAR, NVARCHAR2 타입을 DATE 타입으로 변환한다.
- FORMAT을 생략한다면 CHAR는 Default Date Format과 동일해야 한다.


SQL> SELECT TO_DATE('20110101') AS "Date" FROM DUAL;
SELECT TO_DATE('20110101') AS "Date" FROM DUAL
               *
ERROR at line 1:
ORA-01861: literal does not match format string

- - NLS_DATE_FORMAT 파라미터에 맞게 데이터 형식 변경


SQL> SHOW PARAMETER NLS_DATE_FORMAT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RR

SQL> SELECT TO_DATE('01-JAN-11') AS "Date" FROM DUAL;

Date
------------
01-JAN-11

- - NLS_DATE_FORMAT 파라미터 수정


SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyymmdd' ;

Session altered.

SQL> SELECT TO_DATE('20110101') AS "Date" FROM DUAL;

Date
--------
20110101

- 사용 예제 : 기본 형태 TO_DATE 조회하기


SELECT TO_DATE('20110101') AS "Date" FROM DUAL
UNION ALL
SELECT TO_DATE('01/JAN/2011','dd/mon/yyyy') FROM DUAL
UNION ALL
SELECT TO_DATE('2011-01-01','yyyy-mm-dd') FROM DUAL
UNION ALL
SELECT TO_DATE('20110101','yyyymmdd') FROM DUAL
UNION ALL
SELECT TO_DATE('01012011','ddmmyyyy') FROM DUAL
UNION ALL
SELECT TO_DATE('01012011','mmddyyyy') FROM DUAL
UNION ALL
SELECT TO_DATE('01012011','mmddrrrr') FROM DUAL
UNION ALL
SELECT TO_DATE('20110101','yyyymmdd hh24:mi:ss') FROM DUAL;

Date
--------
20110101
20110101
20110101
20110101
20110101
20110101
20110101
20110101

- 사용 예제 : RR(RRRR) Datetime Format 조회하기
  {tip:title=RR(RRRR) 유형이란?}
  01 ~ 49 = 2001 ~ 2049
  50 ~ 99 = 1950 ~ 1999
  {tip}


SELECT TO_CHAR(TO_DATE('01-JAN-01', 'DD-MON-RR'), 'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-49', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-50', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-99', 'DD-MON-RR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-01', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-49', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-50', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-99', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL;

YEAR
----
2001
2049
1950
1999
2001
2049
1950
1999


SELECT TO_CHAR(TO_DATE('01-JAN-1945', 'DD-MON-RRRR'), 'YYYY') AS "YEAR" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JAN-2099', 'DD-MON-RRRR') ,'YYYY') AS "YEAR" FROM DUAL;

YEAR
----
1945
2099

TO_TIMESTAMP
{info:title=Syntax}
TO_TIMESTAMP(CHAR [,FORMAT][,nlsparam])
{info}
- TO_TIMESTAMP 는 CHAR, VARCHAR2, NCHAR, NVARCHAR2 타입을 TIMESTAMP 타입으로 변환한다.
- FORMAT을 생략한다면 CHAR는 NLS_TIMESTAMP_FORMAT 과 동일해야 한다.
  - 사용 예제


SELECT TO_TIMESTAMP('20110101','yyyymmdd') || ' ---> 하루의 시작'  AS "TO_TIMESTAMP" FROM DUAL
UNION ALL
SELECT TO_TIMESTAMP('2011-01-01 00:00:00:000000000','yyyy-mm-dd hh24:mi:ss:ff') || ' ---> 하루의 시작'
FROM DUAL
UNION ALL
SELECT TO_TIMESTAMP('2011-01-01 23:59:59:999999999','yyyy-mm-dd hh24:mi:ss:ff') || ' ---> 하루의 끝'
FROM DUAL;

TO_TIMESTAMP                                
--------------------------------------------
11/01/01 00:00:00.000000000 ---> 하루의 시작     
11/01/01 00:00:00.000000000 ---> 하루의 시작     
11/01/01 23:59:59.999999999 ---> 하루의 끝

TO_CHAR
{info:title=Syntax}
TO_CHAR(DATE TIME [,FORMAT][,nlsparam])
{info}
- TO_CHAR(DATETIME)는 DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIME ZONE 타입을 VARCHAR2 타입으로 변환한다.
- FORMAT을 생략할 경우 컬럼의 데이터 타입에 따라 다음과 같이 변환된다.


* DATE 데이터는 Default Date Format 으로 변환
* TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE 은 Default Timestamp Format 으로 변환
* TIMESTAMP WITH TIME ZONE 은 Default Timestamp with time zone Format 으로 변환

- 사용 예제


SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd')) AS "Date" FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd'), 'yyyy-mm-dd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd'), 'yyyy/mm/dd') FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('20110101','yyyymmdd'), 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

Date
-------------------
01-JAN-11
2011-01-01
2011/01/01
2011-01-01 00:00:00

ADD_MONTHS

ADD_MONTHS
{info:title=Syntax}
ADD_MONTHS(DATE(시작 일자), INTEGER(더할 월))
{info}
- ADD_MONTHS 함수는 DATE (시작 일자)에 INTEGER (더할 월)을 계산하여 DATE 형식을 리턴한다.
- INTEGER가 양수이면 시작 일자 이후를, 음수이면 이전 날짜를 추출한다.
- INTEGER는 0을 제외한 정수를 사용해야 정화한 데이터를 얻을 수 있으며, 월을 계산할 때 소수점은 무시된다.
- 사용 예제


SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'), 1), 'yyyymmddhh24miss') || ' ---> 20110101,1' AS "ADD_MONTHS"
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),12),'yyyymmddhh24miss') || ' ---> 20110101,12'
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1/24),'yyyymmddhh24miss') || ' ---> 20110101,1/24'
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),-5),'yyyymmddhh24miss') || ' ---> 20110101,-5'
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),-5/24),'yyyymmddhh24miss') || ' ---> 20110101,-5/24'
FROM DUAL
UNION ALL
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101','yyyymmdd'),1.9),'yyyymmddhh24miss') || ' ---> 20110101,1.9'
FROM DUAL;

ADD_MONTHS                        
----------------------------------
20110201000000 ---> 20110101,1    
20120101000000 ---> 20110101,12   
20110101000000 ---> 20110101,1/24 
20100801000000 ---> 20110101,-5   
20110101000000 ---> 20110101,-5/24
20110201000000 ---> 20110101,1.9

MONTHS_BETWEEN

MONTHS_BETWEEN
{info:title=Syntax}
MONTHS_BETWEEN(date1, date2)
{info}
- 두 날짜 사이를 월로 계산하여 NUMBER 타입으로 리턴한다.


date1 = date2 => 0
date1 < date2 => < 0
date1 > date2 => > 0

- 사용 예제


SELECT MONTHS_BETWEEN(TO_DATE('20111001','yyyymmdd'), TO_DATE('20111001','yyyymmdd')) AS "MONTHS_BETWEEN" FROM DUAL
UNION ALL
SELECT MONTHS_BETWEEN(TO_DATE('20111001','yyyymmdd'), TO_DATE('20111201','yyyymmdd'))
FROM DUAL
UNION ALL
SELECT MONTHS_BETWEEN(TO_DATE('20111201','yyyymmdd'), TO_DATE('20111001','yyyymmdd'))
FROM DUAL;

MONTHS_BETWEEN
--------------
             0
            -2
             2

LAST_DAY

LAST_DAY
{info:title=Syntax}
LIST_DAY(DATE)
{info}
- 인자 값에 해당하는 월의 마지막 날을 리턴 (DATE 형식)
- 사용 예제


SELECT TO_CHAR(SYSDATE,'yyyymmdd') AS "LAST_DAY" FROM DUAL
UNION ALL
SELECT TO_CHAR(LAST_DAY(SYSDATE),'yyyymmdd') FROM DUAL;

LAST_DAY
--------
20131108
20131130

NEXT_DAY

NEXT_DAY
{info:title=Syntax}
NEXT_DAY(DATE , format)
{info}
- 인자로 날짜 데이터를 받아 Format(요일) 형식에 맞는 날짜 데이터를 리턴
- 사용 예제


SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Sunday'), 'yyyymmdd hh24:mi:ss') AS "NEXT_DAY" FROM DUAL
UNION ALL
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Monday'), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Tuesday'), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Wednesday'), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Thursday'), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Friday'), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Saturday'), 'yyyymmdd hh24:mi:ss') FROM DUAL;

NEXT_DAY
-----------------
20131110 15:58:02
20131111 15:58:02
20131112 15:58:02
20131113 15:58:02
20131114 15:58:02
20131115 15:58:02
20131109 15:58:02

참고1 - Syntax 관련


[, format] 은 옵션이 아닌 필수이다.

http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions045.htm#OLAXS438

SQL> SELECT NEXT_DAY(SYSDATE) FROM DUAL;
SELECT NEXT_DAY(SYSDATE) FROM DUAL
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

참고2 - ORA-01846 오류 발생시 대처방법


SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Monday'), 'yyyymmdd hh24:mi:ss') FROM DUAL;
SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Monday'), 'yyyymmdd hh24:mi:ss') FROM DUAL
                                *
Error at line 1
ORA-01846: 지정한 요일이 부적합합니다.

SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,'월요일'), 'yyyymmdd hh24:mi:ss') FROM DUAL;

TO_CHAR(NEXT_DAY(SYSDATE,'월요일'),'YYYYMMDDHH24:MI:SS')
-----------------------------------------------------
20131111 17:57:33  

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,'Monday'), 'yyyymmdd hh24:mi:ss') FROM DUAL;

TO_CHAR(NEXT_DAY(SYSDATE,'MONDAY'),'YYYYMMDDHH24:MI:SS')
--------------------------------------------------------
20131111 17:58:44

ROUND

ROUND
{info:title=Syntax}
ROUND(DATE , format)
{info}
- 날짜 데이터를 Format 형식에 맞게 반올림 한다.


* ROUND(DATE)          = 일자 (12시 기준)
* ROUND(DATE, 'YEAR')  = 년 (6월 기준)
* ROUND(DATE, 'Q')     = 분기 (2번째 월 15일 기준)
* ROUND(DATE, 'MONTH') = 월 (15일 기준)
* ROUND(DATE, 'DD')    = 일자 (12시 기준)
* ROUND(DATE, 'HH24')  = 시간 (30분 기준)
* ROUND(DATE, 'MI')    = 분 (30초 기준)
* ROUND(DATE, 'DAY')   = Week가 시작한 일자

- 사용 예제


SELECT TO_CHAR(SYSDATE, 'yyyymmdd hh24:mi:ss') AS "ROUND( DATE[, format] )"
FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'year'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''year'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'q'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''q'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'month'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''month'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'dd'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''dd'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'hh24'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''hh24'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'mi'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''mi'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(ROUND(SYSDATE, 'day'), 'yyyymmdd hh24:mi:ss') || ' ---> ROUND(SYSDATE, ''day'') ' FROM DUAL;

ROUND( DATE[, format] )                        
-----------------------------------------------
20131108 18:16:59                              
20131109 00:00:00                              
20140101 00:00:00 ---> ROUND(SYSDATE, 'year')  
20131001 00:00:00 ---> ROUND(SYSDATE, 'q')     
20131101 00:00:00 ---> ROUND(SYSDATE, 'month') 
20131109 00:00:00 ---> ROUND(SYSDATE, 'dd')    
20131108 18:00:00 ---> ROUND(SYSDATE, 'hh24')  
20131108 18:17:00 ---> ROUND(SYSDATE, 'mi')    
20131110 00:00:00 ---> ROUND(SYSDATE, 'day')

TRUNC

TRUNC
{info:title=Syntax}
TRUNC(DATE , format)
{info}
- 날짜 데이터를 Format 형식에 맞게 절삭 한다.


* TRUNC(DATE)          = 일자
* TRUNC(DATE, 'YEAR')  = 년
* TRUNC(DATE, 'Q')     = 분기
* TRUNC(DATE, 'MONTH') = 월
* TRUNC(DATE, 'DD')    = 일자
* TRUNC(DATE, 'HH24')  = 시간
* TRUNC(DATE, 'MI')    = 분
* TRUNC(DATE, 'DAY')   = Week가 시작한 일자


SELECT TO_CHAR(SYSDATE, 'yyyymmdd hh24:mi:ss') AS "TRUNC( DATE[, format] )"
FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE), 'yyyymmdd hh24:mi:ss') FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'year'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''year'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'q'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''q'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'month'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''month'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'dd'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''dd'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'hh24'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''hh24'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'mi'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''mi'') ' FROM DUAL
UNION ALL
SELECT TO_CHAR(TRUNC(SYSDATE, 'day'), 'yyyymmdd hh24:mi:ss') || ' ---> TRUNC(SYSDATE, ''day'') ' FROM DUAL;

TRUNC( DATE[, format] )                        
-----------------------------------------------
20131108 18:45:47                              
20131108 00:00:00                              
20130101 00:00:00 ---> TRUNC(SYSDATE, 'year')  
20131001 00:00:00 ---> TRUNC(SYSDATE, 'q')     
20131101 00:00:00 ---> TRUNC(SYSDATE, 'month') 
20131108 00:00:00 ---> TRUNC(SYSDATE, 'dd')    
20131108 18:00:00 ---> TRUNC(SYSDATE, 'hh24')  
20131108 18:45:00 ---> TRUNC(SYSDATE, 'mi')    
20131103 00:00:00 ---> TRUNC(SYSDATE, 'day')

EXTRACT

EXTRACT
{info:title=Syntax}
EXTRACT(element FROM datetime)
{info}
- DATE나 TIMESTAMP 데이터만 추출할 때 사용된다.
- 사용 가능한 element 목록


|year| month| day| hour| minute| second| timezone_hour| timezone_minute| timezone_region| timezone_abbr|

- 사용 예제


SELECT TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss') AS sysdate_date
     , TO_CHAR(systimestamp, 'yyyymmdd hh24:mi:ss') AS systimestamp_date
     , EXTRACT (year   FROM sysdate)      AS ext_year
     , EXTRACT (month  FROM sysdate)      AS ext_month
     , EXTRACT (day    FROM sysdate)      AS ext_day
     , EXTRACT (hour   FROM systimestamp) AS ext_hour
     , EXTRACT (minute FROM systimestamp) AS ext_year
     , EXTRACT (second FROM systimestamp) AS ext_second
FROM DUAL;

SYSDATE_DATE      SYSTIMESTAMP_DATE   EXT_YEAR  EXT_MONTH    EXT_DAY   EXT_HOUR   EXT_YEAR EXT_SECOND
----------------- ----------------- ---------- ---------- ---------- ---------- ---------- ----------
20131108 19:13:25 20131108 19:13:25       2013         11          8         10         13  25.705306


SELECT TO_CHAR(TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss'), 'yyyymmdd hh24:mi:ss') AS sysdate_date
     , TO_CHAR(TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss'), 'yyyymmdd hh24:mi:ss') AS systimestamp_date
     , EXTRACT(year   FROM TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss')) AS ext_year
     , EXTRACT(month  FROM TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss')) AS ext_month
     , EXTRACT(day    FROM TO_DATE('20111025 10:45:55','yyyymmdd hh24:mi:ss')) AS ext_day
     , EXTRACT(hour   FROM TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss')) AS ext_hour
     , EXTRACT(minute FROM TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss')) AS ext_minute
     , EXTRACT(second FROM TO_TIMESTAMP('20111025 10:45:55','yyyymmdd hh24:mi:ss')) AS ext_second
FROM DUAL;

SYSDATE_DATE      SYSTIMESTAMP_DATE   EXT_YEAR  EXT_MONTH    EXT_DAY   EXT_HOUR EXT_MINUTE EXT_SECOND
----------------- ----------------- ---------- ---------- ---------- ---------- ---------- ----------
20111025 10:45:55 20111025 10:45:55       2011         10         25         10         45         55

날짜와 관련된 유용한 함수 들. (by sesesejoa) [2013.11.07]

날짜와 관련된 유용한 함수들

TO_DATE & TO_TIMESTAMP & TO_CHAR(datetime)

ADD_MONTHS

MONTHS_BETWEEN

LAST_DAY

NEXT_DAY

ROUND

TRUNC

EXTRACT