\

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

• DATE 조회 - 에러가 발생하는 경우
• Date 조회 - 정상 조회 되는 경우
• 잘못된 날짜 연산들
• 참고

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

날짜 데이터 추출 시 발생하는 에러의 대부분은 DATE 타입에 대한 잘못된 연산으로 발생된다.

• DATE + DATE

SQL> SELECT TO_DATE('20110101','yyyymmdd') + TO_DATE('20110201','yyyymmdd')
  2  FROM DUAL;
SELECT TO_DATE('20110101','yyyymmdd') + TO_DATE('20110201','yyyymmdd')
                                      *
ERROR at line 1:
ORA-00975: date + date not allowed

• CHAR - DATE

SQL> SELECT '20110101' - TO_DATE('20110101','yyyymmdd') FROM DUAL;
SELECT '20110101' - TO_DATE('20110101','yyyymmdd') FROM DUAL
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got DATE

• NUMBER - DATE

SQL> SELECT 20110101 - TO_DATE('20110101','yyyymmdd') FROM DUAL;
SELECT 20110101 - TO_DATE('20110101','yyyymmdd') FROM DUAL
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

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

SQL> SELECT RPAD(TO_CHAR(TO_DATE('20110101', 'yyyymmdd') - TO_DATE('20110101', 'yyyymmdd')),8) || ' ---> DATE   - DATE' AS "Date" FROM DUAL
  2  UNION ALL
  3  SELECT RPAD(TO_CHAR(TO_DATE('20110101', 'yyyymmdd') + 1, 'yyyymmdd'),8) || ' ---> DATE   + NUMBER' FROM DUAL
  4  UNION ALL
  5  SELECT RPAD(TO_CHAR(TO_DATE('20110101', 'yyyymmdd') - 1, 'yyyymmdd'),8)  || ' ---> DATE   - NUMBER' FROM DUAL
  6  UNION ALL
  7  SELECT RPAD(TO_CHAR(TO_DATE('20110101', 'yyyymmdd') + '1', 'yyyymmdd'),8)  || ' ---> DATE   + CHAR' FROM DUAL
  8  UNION ALL
  9  SELECT RPAD(TO_CHAR(TO_DATE('20110101', 'yyyymmdd') - '1', 'yyyymmdd'),8)  || ' ---> DATE   - CHAR' FROM DUAL
 10  UNION ALL
 11  SELECT RPAD(TO_CHAR(1 + TO_DATE('20110101','yyyymmdd'), 'yyyymmdd'),8)  || ' ---> NUMBER - DATE' FROM DUAL
 12  UNION ALL
 13  SELECT RPAD(TO_CHAR('1' + TO_DATE('20110101','yyyymmdd'), 'yyyymmdd'),8)  || ' ---> CHAR   + DATE' FROM DUAL;

Date
-----------------------------
0        ---> DATE   - DATE
20110102 ---> DATE   + NUMBER
20101231 ---> DATE   - NUMBER
20110102 ---> DATE   + CHAR
20101231 ---> DATE   - CHAR
20110102 ---> NUMBER - DATE
20110102 ---> CHAR   + DATE

잘못된 날짜 연산들

• CHAR - NUMBER로 날짜를 계산하는 경우

- 날짜 데이터를 가진 컬럼의 데이터 타입이 CHAR 또는 VARCHAR2 인 경우 발생할 수 있다.
- 단순히 컬럼명만으로 테이터 타입을 유추하지 말고 컬럼의 데이터 타입을 확인하여 날짜 연산을 수행해야 한다.

1. 1. SQL[1] 정상 날짜 데이터 계산

SELECT TO_DATE('20111001', 'yyyymmdd') - 2 AS "Date" FROM DUAL;

Date
--------
11/09/29 

1. 1. SQL[2] 비정상 날짜 데이터 계산

SELECT '20111001' - 2 AS "Date" FROM DUAL;

      Date
----------
  20110999

• 날짜 컬럼 데이터를 NVL() 처리 시

SQL> CREATE TABLE DATE_TEST (A VARCHAR2(8), B VARCHAR2(8));

Table created.

SQL> INSERT INTO DATE_TEST VALUES ('20110101','20110201');

1 row created.

SQL> INSERT INTO DATE_TEST VALUES ('20130101','20110301');

1 row created.

SQL> INSERT INTO DATE_TEST VALUES (NULL,'20110401');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT CASE WHEN NVL(A,SYSDATE) > TO_DATE(B,'YYYYMMDD')
  2  THEN 1 ELSE 0 END
  3  FROM DATE_TEST;
SELECT CASE WHEN NVL(A,SYSDATE) > TO_DATE(B,'YYYYMMDD')
                 *
ERROR at line 1:
ORA-01861: literal does not match format string

SQL> SELECT CASE WHEN TO_DATE(NVL(A, TO_CHAR(SYSDATE, 'YYYYMMDD')), 'YYYYMMDD') >
  2  TO_DATE(B, 'YYYYMMDD') THEN 1 ELSE 0 END AS "DATE_TEST"
  3  FROM DATE_TEST;

 DATE_TEST
----------
         0
         1
         1

SQL> SELECT CASE WHEN NVL(TO_DATE(A, 'YYYYMMDD'), SYSDATE) >
  2  TO_DATE(B, 'YYYYMMDD') THEN 1 ELSE 0 END AS "DATE_TEST"
  3  FROM DATE_TEST;

 DATE_TEST
----------
         0
         1
         1