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

데이터 타입 간 날짜 데이터 제어하기

데이터 타입 간 날짜 데이터 제어하기

DATE 조회 - 에러가 발생하는 경우

DATE + DATE



SQL> SELECT TO_DATE('20131019', 'yyyymmdd') + TO_DATE('20131017', 'yyyymmdd') FROM dual;
SELECT TO_DATE('20131019', 'yyyymmdd') + TO_DATE('20131017', 'yyyymmdd') FROM dual
                                       *
1행에 오류:
ORA-00975: 날짜와 날짜의 가산은 할 수 없습니다

CHAR - DATE



SQL> SELECT '20131019' - TO_DATE('20131017', 'yyyymmdd') FROM dual;
SELECT '20131019' - TO_DATE('20131017', 'yyyymmdd') FROM dual
       *
1행에 오류:
ORA-00932: 일관성 없는 데이터 유형: CHAR이(가) 필요하지만 DATE임

NUMBER - DATE


SQL> SELECT  20131019  - TO_DATE('20131017', 'yyyymmdd') FROM dual;
SELECT  20131019  - TO_DATE('20131017', 'yyyymmdd') FROM dual
        *
1행에 오류:
ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 DATE임

DATE 조회 - 정상 조회 되는 경우



SQL> SELECT 'DATE - DATE' g, TO_CHAR(TO_DATE('20131019', 'yyyymmdd') - TO_DATE('20131017', 'yyyymmdd')) v FROM dual
    UNION ALL SELECT 'DATE + NUMBER', TO_CHAR(TO_DATE('20131019', 'yyyymmdd') + 1, 'yyyymmdd') FROM dual
    UNION ALL SELECT 'DATE - NUMBER', TO_CHAR(TO_DATE('20131019', 'yyyymmdd') - 1, 'yyyymmdd') FROM dual
    UNION ALL SELECT 'DATE + CHAR', TO_CHAR(TO_DATE('20131019', 'yyyymmdd') + '1', 'yyyymmdd') FROM dual
    UNION ALL SELECT 'DATE - CHAR', TO_CHAR(TO_DATE('20131019', 'yyyymmdd') - '1', 'yyyymmdd') FROM dual
    UNION ALL SELECT 'NUMBER + DATE', TO_CHAR(1 + TO_DATE('20131019', 'yyyymmdd'), 'yyyymmdd') FROM dual
    UNION ALL SELECT 'CHAR + DATE', TO_CHAR('1' + TO_DATE('20131019', 'yyyymmdd'), 'yyyymmdd') FROM dual
    ;

G             V
------------- --------
DATE - DATE   2
DATE + NUMBER 20131020
DATE - NUMBER 20131018
DATE + CHAR   20131020
DATE - CHAR   20131018
NUMBER + DATE 20131020
CHAR + DATE   20131020

7 개의 행이 선택되었습니다.

잘못된 날짜 연산들



SQL> SELECT TO_DATE('20131001', 'yyyymmdd') - 2 "Date" FROM dual;

Date
--------
13/09/29


SQL> SELECT '20131001' - 2 "Date" FROM dual;

      Date
----------
  20130999

날짜컬럼 데이터를 NVL(Char


SQL> SELECT dt, NVL(dt, sysdate) dt2
  2    FROM (SELECT '20131019' dt FROM dual
  3          UNION ALL SELECT '' dt FROM dual)
  4  ;

DT       DT2
-------- --------
20131019 20131019
         13/10/18

일자,시간,분,초 계산하기

일자


SQL> SELECT '현재 시각' g, TO_CHAR(sysdate, 'yyyy.mm.dd hh24:mi:ss') d FROM dual
    UNION ALL SELECT   '1일 후', TO_CHAR(sysdate + 1         , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '5일 후', TO_CHAR(sysdate + 5         , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT '1시간 후', TO_CHAR(sysdate + 1/24      , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT '5시간 후', TO_CHAR(sysdate + 5/24      , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '1분 후', TO_CHAR(sysdate + 1/24/60   , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '5분 후', TO_CHAR(sysdate + 5/24/60   , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '1초 후', TO_CHAR(sysdate + 1/24/60/60, 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '5초 후', TO_CHAR(sysdate + 5/24/60/60, 'yyyy.mm.dd hh24:mi:ss') FROM dual
   ;

G         D
--------- -------------------
현재 시각 2013.10.18 01:08:47
1일 후    2013.10.19 01:08:47
5일 후    2013.10.23 01:08:47
1시간 후  2013.10.18 02:08:47
5시간 후  2013.10.18 06:08:47
1분 후    2013.10.18 01:09:47
5분 후    2013.10.18 01:13:47
1초 후    2013.10.18 01:08:48
5초 후    2013.10.18 01:08:52

9 개의 행이 선택되었습니다.

일자


SQL> SELECT '현재 시각' g, TO_CHAR(sysdate, 'yyyy.mm.dd hh24:mi:ss') d FROM dual
    UNION ALL SELECT   '1일 전', TO_CHAR(sysdate - NUMTODSINTERVAL(1, 'DAY'), 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '1일 전', TO_CHAR(sysdate - INTERVAL '1' DAY   , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT '1시간 전', TO_CHAR(sysdate - INTERVAL '1' HOUR  , 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '1분 전', TO_CHAR(sysdate - INTERVAL '1' MINUTE, 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '1초 전', TO_CHAR(sysdate - INTERVAL '1' SECOND, 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT   '1년 전', TO_CHAR(sysdate - NUMTOYMINTERVAL(1, 'YEAR'), 'yyyy.mm.dd hh24:mi:ss') FROM dual
    UNION ALL SELECT '1개월 전', TO_CHAR(sysdate - INTERVAL '1' MONTH, 'yyyy.mm.dd hh24:mi:ss') FROM dual
    ;

G         D
--------- -------------------
현재 시각 2013.10.18 01:25:37
1일 전    2013.10.17 01:25:37
1일 전    2013.10.17 01:25:37
1시간 전  2013.10.18 00:25:37
1분 전    2013.10.18 01:24:37
1초 전    2013.10.18 01:25:36
1년 전    2012.10.18 01:25:37
1개월 전  2013.09.18 01:25:37

8 개의 행이 선택되었습니다.

돌발 퀴즈!

시작시간과 종료시간의 시간차를 시:분:초(00:00:00) 형태로 표현하시오.

돌발 퀴즈


SQL> WITH t AS
    (
    SELECT TO_DATE('20131019130510', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_DATE('20131019180000', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT TO_CHAR(TO_DATE((edt - sdt)*24*60*60, 'sssss'), 'hh24:mi:ss') hms
      FROM t
    ;

HMS
--------
04:54:50

SQL> WITH t AS
    (
    SELECT TO_DATE('20131019110000', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_DATE('20131019120209', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT TO_CHAR(TO_DATE((edt - sdt)*24*60*60, 'sssss'), 'hh24:mi:ss') hms
      FROM t
    ;
SELECT TO_CHAR(TO_DATE((edt - sdt)*24*60*60, 'sssss'), 'hh24:mi:ss') hms
                                        *
7행에 오류:
ORA-01830: 날짜 형식의 지정에 불필요한 데이터가 포함되어 있습니다


SQL> WITH t AS
    (
    SELECT TO_DATE('20131019110000', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_DATE('20131019120209', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT (edt - sdt)*24*60*60     sssss1
         , (edt - sdt)*24*60*60||'' sssss2
      FROM t
   ;

    SSSSS1 SSSSS2
---------- ----------------------------------------
      3729 3728.99999999999999999999999999999999999



SQL> WITH t AS
    (
    SELECT TO_DATE('20131019110000', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_DATE('20131019120209', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT TO_CHAR(TO_DATE(ROUND((edt - sdt)*24*60*60), 'sssss'), 'hh24:mi:ss') hms
      FROM t
    ;

HMS
--------
01:02:09



SQL> WITH t AS
    (
    SELECT TO_DATE('20131019110000', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_DATE('20131020120209', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT TO_CHAR(TO_DATE(ROUND((edt - sdt)*24*60*60), 'sssss'), 'hh24:mi:ss') hms
      FROM t
    ;
SELECT TO_CHAR(TO_DATE(ROUND((edt - sdt)*24*60*60), 'sssss'), 'hh24:mi:ss') hms
                       *
7행에 오류:
ORA-01853: 일의 초는 0과 86399 사이여야 합니다.



SQL> WITH t AS
    (
    SELECT TO_DATE('20131019110000', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_DATE('20131020120209', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT FLOOR(s / (24*60*60)) ||' '||
           TO_CHAR(TO_DATE(MOD(s, 24*60*60), 'sssss'), 'hh24:mi:ss') d_hms
      FROM (SELECT ROUND((edt - sdt)*24*60*60) s FROM t)
   ;

D_HMS
----------
1 01:02:09


SQL> WITH t AS
    (
    SELECT TO_TIMESTAMP('20131019110000', 'yyyymmddhh24miss') sdt -- 시작시간
         , TO_TIMESTAMP('20131020120209', 'yyyymmddhh24miss') edt -- 종료시간
      FROM dual
    )
    SELECT edt - sdt "Interval"
         , EXTRACT(Day    FROM edt - sdt) "Day"
         , EXTRACT(Hour   FROM edt - sdt) "Hour"
         , EXTRACT(Minute FROM edt - sdt) "Minute"
         , EXTRACT(Second FROM edt - sdt) "Second"
      FROM t
    ;

Interval                             Day       Hour     Minute     Second
----------------------------- ---------- ---------- ---------- ----------
+000000001 01:02:09.000000000          1          1          2          9