[Day21][Oracle] 날짜함수 / 변환함수

3. 날짜함수

 

HR

select sysdate
      ,extract(year from sysdate) as "현재년도"
      ,extract(month from sysdate) as "현재월"
      ,extract(day from sysdate) as "현재일"
from dual;

-- 오른쪽 정렬 : 숫자 형태로 나타난다.

 

 

HR

select systimestamp
      ,extract(hour from systimestamp) as "현재시간"
      ,extract(minute from systimestamp) as "현재분"
      ,extract(second from systimestamp) as "현재초"
from dual;

 

 

-- 시간, 분, 초를 구하려면 sysdate가 아닌 systimestamp를 사용해야 한다.

-- 우리나라 시간을 구하고자 한다면 9시간을 더해야 한다.

 

 

HR

select systimestamp
      ,extract(hour from systimestamp)+9 as "현재시간"
      ,extract(minute from systimestamp) as "현재분"
      ,extract(second from systimestamp) as "현재초"
from dual;

 

 

---

 

-- 날짜 + 숫자(일수) → 날짜
-- 날짜 - 숫자(일수) → 날짜

 

HR

select sysdate - 1
     , sysdate
     , sysdate +1
from dual;

 

 

HR

select to_char(sysdate - 1, 'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate + 1, 'yyyy-mm-dd hh24:mi:ss')
from dual;

 

 

HR

select sysdate, add_months(sysdate, -2), sysdate, add_months(sysdate, 2)
from dual;

 

 

현재부터 3년 5개월 뒤를 구해 보자.

HR

select sysdate, add_months(sysdate, 12*3+5) as "3년5개월뒤"
from dual;

-- alias를 쓸 때, 맨 첫 글자가 숫자라면 쌍따옴표를 꼭 표시해 주어야 한다.

 

 

현재부터 3년 5개월 전을 구해 보자.

 

HR

select sysdate, add_months(sysdate, -(12*3+5)) as "3년5개월전"
from dual;

 

---

 

4. 변환함수

 

HR

select sysdate
      , to_char(sysdate, 'yyyy') as "현재년도"
      , to_char(sysdate, 'mm') as "현재월"
      , to_char(sysdate, 'dd') as "현재일"
      , to_char(sysdate, 'hh24') as "현재시간"
      , to_char(sysdate, 'mi') as "현재분"
      , to_char(sysdate, 'ss') as "현재초"
from dual;

 

-- to_char() : 날짜나 숫자를 문자 형태로 변환시켜 주는 함수

-- 왼쪽 정렬 : 문자 형태로 나타난다.

 

HR

select 2000+1
      ,'2000' + 1
      ,'2000'
      ,to_number('2000')
from dual;

-- to_number(숫자형태의문자데이터) : 숫자 형태의 문자 데이터를 실제 숫자타입으로 변환시켜주는 것이다.

 

---

 

문제1 ▷▷ 아래와 같은 결과가 나오도록 하세요.

 

 

HR

select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,jubun as 주민번호
      ,case
       when substr(jubun,1,1) in('0','1') then extract(year from sysdate)-(2000+substr(jubun,1,2))+1
       else extract(year from sysdate)-(1900+substr(jubun,1,2))+1
       end as 현재나이
      ,case 
       when substr(jubun, 7, 1) in ('1', '3') then '남'
       else '여'
       end as 성별
from employees
order by employee_id;

 

---

 

문제2 ▷▷ employees 테이블에서 사원들이 63세가 되는 해를 나타내세요.

 

HR

select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,jubun as 주민번호
      ,case
       when substr(jubun,1,1) in('0','1') then extract(year from sysdate)-(2000+substr(jubun,1,2))
       else extract(year from sysdate)-(1900+substr(jubun,1,2))
       end as 현재나이
      ,case 
       when substr(jubun, 7, 1) in ('1', '3') then '남'
       else '여'
       end as 성별
      ,case
       when substr(jubun,1,1) in('0','1') then to_char(add_months(sysdate, 12*(63-((extract(year from sysdate)-(2000+substr(jubun,1,2)))))),'yyyy')
       else to_char(add_months(sysdate, 12*(63-((extract(year from sysdate)-(1900+substr(jubun,1,2)))))),'yyyy')
       end as "63세가되어지는년도" 
from employees
order by employee_id;

 

 

---

 

Ⅰ. inline view

 

employees 테이블에서 성별이 여자인 사원만 조회하시오.

 

HR

select V.*
from 
(
select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,jubun as 주민번호
      ,extract(year from sysdate) - (to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end) as 현재나이
      ,case 
       when substr(jubun, 7, 1) in ('1', '3') then '남' else '여' end as 성별
       from employees
order by employee_id
) V -- V가 inline view 이다.
where V."성별" = '여';

 

 

 

employees 테이블에서 성별이 여자이고 나이가 20대, 40대인 사원만 조회하시오.

 

select V.*
from
(
select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,jubun as 주민번호
      ,extract(year from sysdate) - (to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end) as 현재나이
      ,case 
       when substr(jubun, 7, 1) in ('1', '3') then '남' else '여' end as 성별
       from employees
order by employee_id
) V -- V가 inline view 이다.
where V."성별" = '여' and 
      trunc(V."현재나이", -1) in(20, 40)
order by V."현재나이";

 

 

 

-- 문제2도 inline view를 사용하여 간략화 할 수 있다.

 

select V.*
       ,to_char(add_months(sysdate, (63-V."현재나이")*12), 'yyyy') as "63세가 되어지는 년도2"
from
(
select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,jubun as 주민번호
      ,extract(year from sysdate) - (to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end) as 현재나이
      ,case 
       when substr(jubun, 7, 1) in ('1', '3') then '남' else '여' end as 성별
from employees
)V
order by V."사원번호";

 

 

-- V는 뒤에 *가 나오는 경우 제외하고 생략할 수 있다.