[복습] SQL | Oracle 날짜 함수: 날짜 추출 함수(extract, to_char)

앞선 글에서 emp 테이블에서 각 직원의 이름과 입사연도를 출력하라는 연습문제가 있었다.

이제까지 배운 문자 함수로는 풀 수가 없었다. 특히 substr로 풀기에는 아래처럼 접근이 적절치 않았다.

이러한 결말은 바로 다음과 같기 때문이다.

 

날짜 타입은 각 DBMS마다 기본 저장 포맷이 있다.

내가 테스트하고 있는 orcl이라는 현 DB는 YY/MM/DD라는 포맷을 가지고 있으며,

날짜 데이터 출력 시 사용한 개발 툴의 날짜 출력 포맷에 따라 출력된다.

내가 사용하는 개발 툴은 orange이며 이 경우는 YYYY/MM/DD 이다.

따라서 substr을 쓰면 원하는 포맷대로 출력이 되지 않는다.

 

먼저 내가 현 DB에 저장된 날짜 포맷이 YY/MM/DD임을 아는 경우 월을 추출하는 방법을 써보겠다.

우선 이 방법은 추천하지 않는다. 내가 어느 곳에 가서든지 사용할 수 있는 코드를 만들기 위해서는 어떤 포맷인지도 모르는 곳에서도 적용할 수 있는 코드를 짜야 하기 때문이다.

일단은 포맷을 아는 경우에 대하여 알아보자.

 

위처럼 원본 데이터 날짜를 매번 확인하며 접근하는 것은 번거롭다. 그렇기 때문에 substr 은 적절하지 않다고 한 것이다.

날짜 포맷이 무엇이던간에 원하는 날짜를 출력해야 한다.

 

그러므로 날짜 포맷을 알 수 없더라도 원하는 날짜를 출력하는 방법

첫 번째로는 현 세션의 날짜 포맷을 사용자가 지정하는 것이다.

이 방법도 선생님은 비추하셨다. 이는 관리자만 접근이 가능한 방법이므로 실습 없이 바로 작성하고 넘어가겠다.

 

alter session set nls_date_format = 'YYYY/MM/DD';

 

로 작성하면 날짜 형태가 YYYY/MM/DD로 바뀌며 그 다음에

 

select hiredate,

           substr(hiredate, 6, 2)

   form emp;

 

하면 된다. 그러나 다른 세션에서 수행하면 이대로 나오지 않는다. 본 세션에서만 적용되기 때문이다.

즉 orange를 새로 실행하여 수행하면 나오지 않는다. 

즉 이 쿼리를 들고 다른 회사에서 테스트하면 나오지 않는다는 것.

 

그렇기 때문에 다음과 같은 방법을 사용하자!

★날짜 함수를 사용하는 것이다.

여기서 extract(month from hiredate)를 사용할 경우 날짜 숫자가 리턴되며 숫자이기 때문에 02월 이렇게 출력이 되지 않고 2라고 출력된다.

그리고 to_char(hiredate, 'MM')을 출력할 경우에는 날짜 문자가 리턴되어, 문자이기 때문에 02월이라고 출력이 된다.

 

참고로 성능은  select 절이 아닌 where 절에서 갈린다.

select *

  from emp

 where substr(hiredate, 6, 2) = '02';

하게 되면 성능은 떨어진다.

 

 

문제 하나를 풀어보자.

emp에서 1981년 6월 9일 입사자를 출력해보자.

이렇게 두 방법으로 풀 수 있다.

위 방법은 내가 푼 방법인데.. 어느 게 나은 건지 잘 모르겠다.

결국 두 번째 방법도 포맷이 YYYY/MM/DD라는 걸 알아야 풀 수 있는 문제가 아닌가 싶어서.. 더 고민해봐야겠다.

 

 

student 테이블에서 1975년 3월 3일에 태어난 학생의 이름과 생년월일을 출력하자.

이렇게 출력할 수 있다. 그런데 이 테이블에는 주민등록번호도 존재한다.

따라서 주민등록번호로도 추출할 수 있다. 이 방법으로도 풀이해보자.

 

이렇게 풀 수 있겠다.

 

 

professor 테이블에서 상반기에 입사한 교수의 이름, 직급, 입사일을 출력해보자

위에는 내 풀이 방법이다. 여기서 나는 처음 where 절에 부등호가 아닌 in을 썼다. 그런데 extract 는 숫자로 리턴되기 때문에 부등호로 바꾸어 작성하였다.

그런데 선생님은 아래와 같은 방법으로도 풀이해주셨다.

★여기서 '06' 의 작은따옴표 필수라고 해주셨다. 그리고 작은따옴표를 포함하더라도 '6' 이라고 써도 안된다고 하셨다. ★

숫자를 문자의 형태로 비교하게 되면 잘못된 비교 결과가 나올 수도 있다고 한다.

즉 컴퓨터는 아래와 같이 인식한다고 한다.

'11' < '6'   

그리고

'11' > '06'

 

즉 여기서 문자 정렬을 다음과 같이 인식한다고 하기 때문이다.

1, 123, 1233, 2, 234, 2343, 3.......

절대 숫자의 크기처럼 정렬되지 않는다.

 

이렇게 날짜 추출 함수까지 공부하였다.