[Day22][Oracle] 날짜함수 / 변환함수 / 기타함수

3. 날짜 함수

 

 

현재 시각을 알려주는 함수

 

HR

select sysdate, current_date, localtimestamp, current_timestamp, systimestamp
from dual;

 

 

-- 날짜의 기본적인 표현방식은 RR/MM/DD/이다.
-- RR은 00~49SMS 2000~2049 이고, 50~99는 1950~1999이다.

 

 

HR

select *
from v$timezone_names;

 

 

-- 날짜 - 날짜 = 숫자(단위가 일수)

 

HR

select sysdate+3 - sysdate
from dual;

 

 

 

근무일수를 구해 보자.

 

HR

select employee_id, first_name || ' ' || last_name,
       hire_date, sysdate - hire_date
from employees;

 

 

-- sysdate가 현재 시간을 받아오기 때문에 뒤의 숫자가 매번 바뀐다.

 

 

HR

select employee_id
       ,first_name || ' ' || last_name
       ,hire_date as "입사일자"
       ,to_char(hire_date, 'yyyy-mm-dd hh24:mi:ss')
       ,to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
       ,trunc(sysdate - hire_date) as "근무일수"
from employees;

 

 

 

근무개월수를 구해 보자.

 

HR

select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,trunc(sysdate - hire_date) as 근무일수
      ,trunc(months_between(sysdate, hire_date)) as 근무개월수
from employees;

 

 

---

 

사원번호 사원명 현재나이 정년퇴직일(63세가 되는 년도에 입사한 일자)를 나타내 보자.

 

HR

select V.*
      ,to_char(add_months(sysdate, (63-V."현재나이")*12), 'yyyy') || substr(V."입사일자", 5) as 정년퇴직일
from
(
select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,extract(year from sysdate) - (substr(jubun, 1, 2) + case when substr(jubun, 7, 1) in ('1', '2') then 1900 else 2000 end) + 1 as 현재나이
      , to_char(hire_date, 'yyyy-mm-dd') as 입사일자
from employees
) V;

 

 

 

예상퇴직금을 구해 보자.

-- 퇴직금 : 근무년수 × 월급

 

HR

select V.*
      ,to_char(add_months(sysdate, (63-V."현재나이")*12), 'yyyy') || substr(V."입사일자", 5) as 정년퇴직일
      , trunc(months_between(to_date(to_char(add_months(sysdate, (63-V."현재나이")*12), 'yyyy') || substr(V."입사일자", 5),'yyyy-mm-dd'), to_date(V."입사일자",'yyyy-mm-dd'))/12) * V."월급" as 퇴직금
from
(
select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,extract(year from sysdate) - (substr(jubun, 1, 2) + case when substr(jubun, 7, 1) in ('1', '2') then 1900 else 2000 end) + 1 as 현재나이
      ,to_char(hire_date, 'yyyy-mm-dd') as 입사일자
      ,nvl(salary + (salary*commission_pct), salary) as 월급 
from employees
order by employee_id
) V;

 

 

---

▷ last_day(특정날짜)

: 특정날짜가 포함된 달력에서 맨 마지막날을 알려주는 것이다.

 

HR

select sysdate, last_day(sysdate)
from dual;

 

 

 

▷ next_day(특정날짜, '일') ~ next_day(특정날짜, '토')

: 특정날짜로부터 다음번에 돌아오는 가장 빠른 '일'의 날짜를 알려주는 것

: 특정날짜로부터 다음번에 돌아오는 가장 빠른 '토'의 날짜를 알려주는 것

 

HR

select sysdate
       ,next_day(sysdate, '월')
       ,next_day(sysdate, '금')
       ,next_day(sysdate, '토')
from dual;

 

 

 

▷ 현재 날짜와 '2019-09-20' 문자열 비교

 

HR

select case '2019-09-20'
       when to_char(sysdate, 'yyyy-mm-dd') then '오늘은 2019-09-20 입니다.'
       else '오늘은 2019-09-20 아닙니다.'
       end
from dual;

 

 

---

이자 내는 날짜 구하기

 

HR

create table tbl_loan
(gejanum        varchar2(10)   -- 통장번호
,loanmoney      number         -- 대출금
,interestrate   number(2,2)    -- 이자율
,paymentdate    varchar2(2)    -- 이자를내는날짜 '01', '05', '10', '21', 매월말일 '00'
);           

insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
values('10-1234-01', 5000, 0.03, '01');

insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
values('10-1234-02', 5000, 0.03, '10');

insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
values('10-1234-03', 5000, 0.03, '21');

insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
values('10-1234-04', 5000, 0.03, '00');

insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
values('10-1234-05', 5000, 0.03, '05');

insert into tbl_loan(gejanum, loanmoney, interestrate, paymentdate)
values('10-1234-06', 5000, 0.03, '15');


commit;

select *
from tbl_loan;

 

 

HR

select T.gejanum, T.loanmoney, T.interestrate, T.paymentdate
      ,T.REALPREVIOUSDAY
      ,decode(to_char(T.REALPREVIOUSDAY, 'd'), '1', T.REALPREVIOUSDAY+1
                                             , '7', T.REALPREVIOUSDAY+2
                                                  , T.REALPREVIOUSDAY)
      ,T.REALCURRENTDAY
      ,decode(to_char(T.REALCURRENTDAY, 'd'), '1', T.REALCURRENTDAY+1
                                            , '7', T.REALCURRENTDAY+2
                                                 , T.REALCURRENTDAY)                                            
from
(
select gejanum, loanmoney, interestrate, paymentdate
      , case when to_char(previousday, 'mmdd') in ('0301','0505','0606','0815','1003','1225')
             then previousday+1 
             else previousday
             end as REALPREVIOUSDAY
      , case when to_char(currentday, 'mmdd') in ('0301','0505','0606','0815','1003','1225')
             then currentday+1 
             else currentday
             end as REALCURRENTDAY
      
from
(
select gejanum, loanmoney, interestrate, paymentdate
      ,decode(paymentdate, '00', last_day(add_months(sysdate, -1))
                               , to_date(to_char(add_months(sysdate, -1), 'yyyy-mm-') || paymentdate, 'yyyy-mm-dd')
                               ) as PREVIOUSDAY
      ,decode(paymentdate, '00', last_day(sysdate)
                               , to_date(to_char(sysdate, 'yyyy-mm-') || paymentdate, 'yyyy-mm-dd')
                               ) as CURRENTDAY                         
from tbl_loan
) V
) T

 

 

 

이번 달 이자금액을 구하시오.

 

HR

select T.gejanum, T.loanmoney, T.interestrate, T.paymentdate
      , decode(to_char(T.REALCURRENTDAY, 'd'), '1', T.REALCURRENTDAY+1
                                            , '7', T.REALCURRENTDAY+2
                                                 , T.REALCURRENTDAY) as "이번달이자지급날짜"
      ,(decode(to_char(T.REALCURRENTDAY, 'd'), '1', T.REALCURRENTDAY+1
                                            , '7', T.REALCURRENTDAY+2
                                                 , T.REALCURRENTDAY)          
      -decode(to_char(T.REALPREVIOUSDAY, 'd'), '1', T.REALPREVIOUSDAY+1
                                             , '7', T.REALPREVIOUSDAY+2
                                                  , T.REALPREVIOUSDAY)) * (T.loanmoney*T.interestrate/12) as "이번달이자금액"
from
(
select gejanum, loanmoney, interestrate, paymentdate
      , case when to_char(previousday, 'mmdd') in ('0301','0505','0606','0815','1003','1225')
             then previousday+1 
             else previousday
             end as REALPREVIOUSDAY
      , case when to_char(currentday, 'mmdd') in ('0301','0505','0606','0815','1003','1225')
             then currentday+1 
             else currentday
             end as REALCURRENTDAY
      
from
(
select gejanum, loanmoney, interestrate, paymentdate
      ,decode(paymentdate, '00', last_day(add_months(sysdate, -1))
                               , to_date(to_char(add_months(sysdate, -1), 'yyyy-mm-') || paymentdate, 'yyyy-mm-dd')
                               ) as PREVIOUSDAY
      ,decode(paymentdate, '00', last_day(sysdate)
                               , to_date(to_char(sysdate, 'yyyy-mm-') || paymentdate, 'yyyy-mm-dd')
                               ) as CURRENTDAY                         
from tbl_loan
) V
) T;

 

 

---

4. 변환 함수

 

HR

select '2019-09-10', to_date('2019-09-10', 'yyyy-mm-dd')
from dual;

 

-- 문자를 실제 날짜 타입으로 바꾸어 준다.

 

 

 

select sysdate
      ,to_char(sysdate, 'day')
      ,to_char(sysdate, 'dy')
      ,to_char(sysdate, 'd')
from dual;

 

 

-- 윈도우와 리눅스 호환을 위해서 세 번째 방법(숫자)을 많이 사용한다.

 

 

HR

select decode(to_char(sysdate, 'd'), '1', '일'
                                   , '2', '월'
                                   , '3', '화'
                                   , '4', '수'
                                   , '5', '목'
                                   , '6', '금'
                                   , '7', '토'
                                   ) as "오늘의요일명"
from dual;

 

 

▷ 분기 나타내기

 

HR

select to_char(sysdate, 'q')
from dual;

 

-- 1분기 : 1월~3월
-- 2분기 : 4월~6월
-- 3분기 : 7월~9월
-- 4분기 : 10월~12월

 

 

 

HR

select to_char(sysdate, 'd')
      ,to_char(sysdate, 'dd')
      ,to_char(sysdate, 'ddd')
from dual;

 

 

 

HR

select to_char(sysdate, 'sssss')
from dual;

 

-- 자정(0시 0분 0초)부터 현재까지 흘러간 초를 확인할 수 있다.

 

 

HR

select 123456789
      ,to_char(123456789, '999,999,999')
      ,to_char(123456789, '$999,999,999')
      ,to_char(123456789, 'L999,999,999')
from dual;

 

 

-- L : 해당 지역 화폐 단위

 

 

---

5. 기타함수

 

 

▷ rank : 등수 구하기

▷ dense : 서열(순위) 구하기

 

HR

select employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,salary
      , rank() over(order by salary desc) AS 등수
      , dense_rank() over(order by salary desc) AS 서열
from employees;

 

 

 

문제 ▷▷ 사원번호, 사원명, 월급, 월급의등수, 월급의서열을 나타내세요.

 

HR

select 사원번호, 사원명, 월급
      ,rank() over(order by 월급 desc) AS 월급의등수
      ,dense_rank() over(order by 월급 desc) AS 월급의서열
from
(
select employee_id AS 사원번호
     , first_name || ' ' || last_name AS 사원명
     , nvl(salary*(salary*commission_pct), salary) AS 월급
from employees
) V;

 

 

---

 

월급의 등수가 1등부터 10등까지인 사원들만 사원번호, 사원명, 월급, 월급의등수를 나타내세요.

 

HR

select employee_id AS 사원번호
     , first_name || ' ' || last_name AS 사원명
     , nvl(salary*(salary*commission_pct), salary) AS 월급
     , rank() over(order by nvl(salary*(salary*commission_pct), salary) desc) AS 월급의등수
from employees
where rank() over(order by nvl(salary*(salary*commission_pct), salary) desc) <= 10;HR

 

 

-- rank 또는 dense_rank는 where절에 사용할 수 없다.

-- inline view를 사용해야 한다.

 

 

HR

select *
from
(
select employee_id AS 사원번호
     , first_name || ' ' || last_name AS 사원명
     , nvl(salary*(salary*commission_pct), salary) AS 월급
     , rank() over(order by nvl(salary*(salary*commission_pct), salary) desc) AS 월급의등수
from employees
) V
where 월급의등수 <= 10;

 

 

---

 

문제 ▷▷ employees 테이블에서 연령대가 20대, 30대인 남자와 연령대가 10대, 40대인 여자들만 사원번호, 사원명, 주민번호, 현재나이로 나타내세요.

 

HR

select V."사원번호", V."사원명", V."주민번호", V."현재나이"
from
(select employee_id as 사원번호
      ,first_name || ' ' || last_name as 사원명
      ,jubun as 주민번호
      -- 현재나이 : 현재년도 - 태어난 년도 + 1
      ,extract(year from sysdate) - (substr(jubun, 1,2) + (case when substr(jubun, 7, 1) 
      in ('1','2') then 1900 else 2000 end)) + 1 as 현재나이
      ,case when substr(jubun, 7, 1) in('1', '3') then '남'
      else '여' end as 성별
from employees
order by employee_id
) V
where (V."성별" = '남' and trunc(V."현재나이",-1) in(20, 30))
     or (V."성별" = '여' and trunc(V."현재나이",-1) in(10, 40))
order by 성별, 현재나이;

 

 

 

▷ greatest()

: 나열된 것 중 가장 큰 값을 가지는 것만 추출

▷ least()

: 나열된 것 중 가장 작은 값을 가지는 것만 추출

 

HR

select greatest(10,90,100,80)
      ,least(10,90,100,80)
from dual;

 

 

-- 숫자 뿐만 아니라 문자도 가능하다.

 

HR

select greatest('김유신', '윤봉길', '허준', '고두심')
      ,least('김유신', '윤봉길', '허준', '고두심')
from dual;

 

 

▷ lag

: 어떤 행의 바로 앞의 몇 번째 행을 가리키는 것

▷ lead

: 어떤 행의 바로 뒤의 몇 번째 행을 가리키는 것

 

HR

select lag(first_name || ' ' || last_name) over(order by salary desc) -- 내 SALARY보다 한 단계 많은 사원명
      ,lag(salary) over(order by salary desc) -- 내 SALARY보다 한 단계 많은 SALARY
      ,employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,salary
      ,lead(first_name || ' ' || last_name) over(order by salary desc) -- 내 SALARY보다 한 단계 적은 사원명 
      ,lead(salary) over(order by salary desc) -- 내 SALARY보다 한 단계 적은 SALARY
from employees;

 

 

HR

select lag(first_name || ' ' || last_name, 2) over(order by salary desc)
      ,lag(salary, 2) over(order by salary desc)
      ,employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,salary
      ,lead(first_name || ' ' || last_name, 2) over(order by salary desc)
      ,lead(salary, 2) over(order by salary desc)
from employees;

 

 

-- 자신을 기준으로 두 칸 위, 두 칸 아래

-- 아무 숫자도 적혀 있지 않으면 1이 생략되어져 있다.

 

 

Null 값 대신 다른 값 입력하기

 

 

 

HR

select lag(first_name || ' ' || last_name, 1, ' ') over(order by salary desc)
      ,lag(salary, 1, 0) over(order by salary desc)
      ,employee_id
      ,first_name || ' ' || last_name AS ENAME
      ,salary
      ,lead(first_name || ' ' || last_name, 1, ' ') over(order by salary desc)
      ,lead(salary, 1, 0) over(order by salary desc)
from employees;

 

 

 

-- Null 값 대신 공백(' ')과 0이 입력되었다.