NLS_DATE_FORMAT 파라미터와 데이터 변환

NLS_DATE_FORMAT 파라미터는 날짜 값의 암시적 데이터 변환과 포맷이 지정되지 않은 변환 함수의 기본 포맷으로 사용된다.

테스트를 위해 아래와 같이 테이블을 생성하자.

1 2 3

-- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 AS SELECT DATE '2050-01-02' AS c1 FROM DUAL;

NLS_DATE_FORMAT 파라미터를 YYYY-MM-DD로 설정하고 쿼리를 수행하면 4개의 쿼리 모두 정상적인 결과가 반환된다. 2-2번 쿼리는 우선순위, 2-4번 쿼리는 LIKE 연산자에 의해 c1 열의 데이터가 암시적으로 변환된다. 2-3, 2-5번 쿼리는 변환 함수로 명시적 데이터 변환을 수행했지만 포맷을 지정하지 않았다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

-- 2-1 ALTER SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD";   세션이 변경되었습니다.   -- 2-2 SELECT * FROM t1 WHERE c1 = '20500102';   C1 ---------- 2050-01-02   1개의 행이 선택되었습니다.   -- 2-3 SELECT * FROM t1 WHERE c1 = TO_DATE ('20500102');   C1 ---------- 2050-01-02   1개의 행이 선택되었습니다.   -- 2-4 SELECT * FROM t1 WHERE c1 LIKE '2050%';   C1 ---------- 2050-01-02   1개의 행이 선택되었습니다.   -- 2-5 SELECT * FROM t1 WHERE TO_CHAR (c1) = '2050-01-02';   C1 ---------- 2050-01-02   1개의 행이 선택되었습니다.

NLS_DATE_FORMAT 파라미터를 MM-DD-YYYY로 설정하고 쿼리를 다시 수행하면, 3-2, 3-3번 쿼리는 에러가 발생하고, 3-4, 3-5번 쿼리는 결과가 반환되지 않는다. 로컬 개발 환경과 운영 서버 환경의 NLS_DATE_FORMAT 파라미터 불일치는 결과 오류와 에러 발생의 원인이 될 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

-- 3-1 ALTER SESSION SET NLS_DATE_FORMAT = "MM-DD-YYYY";   세션이 변경되었습니다.   -- 3-2 SELECT * FROM t1 WHERE c1 = '20500102';   ORA-01843: 지정한 월이 부적합합니다.   -- 3-3 SELECT * FROM t1 WHERE c1 = TO_DATE ('20500102');   ORA-01843: 지정한 월이 부적합합니다.   -- 3-4 SELECT * FROM t1 WHERE c1 LIKE '2050%';   선택된 레코드가 없습니다.   -- 3-5 SELECT * FROM t1 WHERE TO_CHAR (c1) = '2050-01-02';   선택된 레코드가 없습니다.

아래와 같이 포맷을 지정한 변환 함수로 명시적 데이터 변환을 수행하면 NLS_DATA_FORMAT 파라미터와 관계없이 정상적인 결과가 반환된다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

-- 4-1 ALTER SESSION SET NLS_DATE_FORMAT = "DD-MM-YYYY";   세션이 변경되었습니다.   -- 4-2 SELECT * FROM t1 WHERE c1 = TO_DATE ('20500102', 'YYYYMMDD');   C1 ---------- 02-01-2050   1개의 행이 선택되었습니다.   -- 4-3 SELECT * FROM t1 WHERE c1 = TO_DATE ('20500102', 'YYYYMMDD');   C1 ---------- 02-01-2050   1개의 행이 선택되었습니다.   -- 4-4 SELECT * FROM t1 WHERE c1 >= TO_DATE ('20500102', 'YYYYMMDD') AND c1 < TO_DATE ('20510101', 'YYYYMMDD');   C1 ---------- 02-01-2050   1개의 행이 선택되었습니다.   -- 4-5 SELECT * FROM t1 WHERE c1 = DATE '2050-01-02';   C1 ---------- 02-01-2050   1개의 행이 선택되었습니다.