by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




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

TO_DATE & TO_TIMESTAMP & TO_CHAR(datetime)

  • TO_DATE
    Syntax

    TO_DATE(CHAR [,FORMAT][,nlsparam])

    • 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 조회하기
      RR(RRRR) 유형이란?

      01 ~ 49 = 2001 ~ 2049
      50 ~ 99 = 1950 ~ 1999

      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
    Syntax

    TO_TIMESTAMP(CHAR [,FORMAT][,nlsparam])

    • 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
    Syntax

    TO_CHAR(DATE TIME [,FORMAT][,nlsparam])

    • 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
    Syntax

    ADD_MONTHS(DATE(시작 일자), INTEGER(더할 월))

    • 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
    Syntax

    MONTHS_BETWEEN(date1, date2)

    • 두 날짜 사이를 월로 계산하여 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
    Syntax

    LIST_DAY(DATE)

    • 인자 값에 해당하는 월의 마지막 날을 리턴 (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
    Syntax

    NEXT_DAY(DATE [, format])

    • 인자로 날짜 데이터를 받아 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
    Syntax

    ROUND(DATE [, format])

    • 날짜 데이터를 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
    Syntax

    TRUNC(DATE [, format])

    • 날짜 데이터를 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
    Syntax

    EXTRACT(element FROM datetime)

    • 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
      

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.