NLS_DATE_FORMAT 파라미터와 데이터 변환

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

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

-- 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번 쿼리는 변환 함수로 명시적 데이터 변환을 수행했지만 포맷을 지정하지 않았다.

-- 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 파라미터 불일치는 결과 오류와 에러 발생의 원인이 될 수 있다.

-- 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 파라미터와 관계없이 정상적인 결과가 반환된다.

-- 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개의 행이 선택되었습니다.