[SQL] 날짜 함수 ADD_MONTHS, NEXT_DAY, LAST_DAY, EXTRACT, MONTHS_BETWEEN

#1 날짜 함수

1. SYSDATE -- 사용빈도(★★★★★)
 - 시스템이 제공하는 날짜 및 시각정보(년, 월, 일, 시, 분, 초) 반환 -- 화면 오른쪽 하단에 표시됨
 - '+', '-'연산이 가능하며, 날짜 자료 사이의 뺄셈의 결과는 두 날짜 사이의 날(=일) 수(Days)를 반환 

    -- 정오를 기준으로 반올림될 수 있음

 

2. ADD_MONTHS(d, n) -- d: 날짜, n: 월에 더할 숫자, 결과: 날짜
 - 날짜 자료 d에 포함된 월에 n을 더한 날짜 반환
  사용예)
     SELECT EMPLOYEE_ID,  
                    EMP_NAME,
                    HIRE_DATE,  
                    ADD_MONTHS(HIRE_DATE,3)
        FROM HR.EMPLOYEES;

3. NEXT_DAY(d, c) -- 사용빈도(★★☆☆☆)
 - 날짜 자료 d 이후에 처음 만나는 c요일의 날짜 반환
 - c는 '월요일', '월',... , '일요일', '일' 중 하나 기술 -- 한글만 인식
  사용예)
    SELECT NEXT_DAY(SYSDATE, '금') FROM DUAL; 
    SELECT NEXT_DAY(SYSDATE, '토요일') FROM DUAL;
    
4. LAST_DAY(d) -- 사용빈도(★★★★☆) , 년, 월, 일이 필요함
 - 날짜 자료 d에 포함된 월의 마지막 일자를 반환
 - 주로 2월의 마지막 일자나 확정되지 않은 월의 마지막 일자가 필요한 경우 사용
  사용예) 회원테이블에서 각 회원의 생년월일에 포함된 월의 마지막일자를 조회하시오.
    SELECT MEM_ID, 
                   MEM_NAME, 
                   MEM_BIR, 
                   LAST_DAY(MEM_BIR)
       FROM MEMBER;

  사용예) 키보드로 연도와 월을 입력받아 해당 월에 발생된 매입수량과 매입금액 합계를 출력하시오.
      ACCEPT P_PERIOD PROMPT '년도와 월(YYYYMM) 입력; ' -- 출력할 메시지
      DECLARE
         L_SDATE DATE := TO_DATE('&P_PERIOD'||'01'); -- 시작일자, ex) 202006 입력하면 20200601을 날짜 형식으로 반환
         L_EDATE DATE := LAST_DAY(L_SDATE); -- 월의 종료일자, 꼭 날짜 타입이어야 함
         L_SQTY NUMBER:=0; -- 수량 합계, 숫자 변수 사용 시 초기화는 반드시 해야 함, 초기화 안 하면 무한반복
         L_SUM NUMBER:=0; -- 매입금액 합계, 숫자 변수 사용 시 초기화는 반드시 해야 함
      BEGIN
         SELECT SUM(A.BUY_QTY), SUM(A.BUY_QTY*B.PROD_COST) INTO L_SQTY, L_SUM
            FROM BUYPROD A, PROD B
          WHERE  A.BUY_PROD=B.PROD_ID
                AND  A.BUY_DATE BETWEEN L_SDATE AND L_EDATE;
       
         DBMS_OUTPUT.PUT_LINE('기간 : '||L_SDATE||'~'||L_EDATE);
         DBMS_OUTPUT.PUT_LINE('매입수량 합계 : '||TO_CHAR(L_SQTY, '9,999')||'개');
         DBMS_OUTPUT.PUT_LINE('매입금액 합계 : '||TO_CHAR(L_SUM, '999,999,999')||'원');
         DBMS_OUTPUT.PUT_LINE('----------------------------------');   
      END; 

5. EXTRACT(fmt FROM d)  -- 사용빈도(★★★★★)
 - 주어진 날짜 자료 d에서 'fmt'로 정의된 값을 반환
 - 'fmt'는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 중 하나
 - 결과는 숫자형 자료임
  사용예) 회원 테이블에서 회원들의 생년월일(MEM_BIR) 칼럼의 값을 사용하여 나이를 계산하시오.
               Alias 회원번호, 회원명, 생년월일, 나이
         SELECT MEM_ID AS 회원번호,
                        MEM_NAME AS 회원명,
                        MEM_BIR AS 생년월일,
                        EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) AS 나이
            FROM MEMBER;

  사용예) 회원 테이블에서 이번 달 생일인 회원을 찾아 보너스 마일리지를 100포인트씩 지급하려한다.
               이번달 생일인 회원정보를 조회하시오.
                Alias는 회원번호,회원명,생년월일,원 마일리지,         
         SELECT MEM_ID AS 회원번호,
                        MEM_NAME AS 회원명,
                        MEM_BIR AS 생년월일,
                        MEM_MILEAGE AS 원마일리지,
                        MEM_MILEAGE + 100 AS 변경마일리지 
            FROM MEMBER           
         WHERE EXTRACT(MONTH FROM SYSDATE) = EXTRACT(MONTH FROM MEM_BIR); -- 이번 달이 생일인 회원
  

6. MONTHS_BETWEEN(d1, d2) -- 사용빈도(★★☆☆☆)
 - 두 날짜 자료 d1과 d2사이의 개월 수를 반환
 - 경력 계산 등 날짜 자료 중 개월 수가 필요한 경우 사용
  사용예) 사원 테이블에서 80번 부서의 직원들의 근속연수를 계산하시오.

               근속연수는 월 단위까지(XX 년 XX월) 계산하시오.
                Alias 사원번호,사원명,입사일,직무 코드,근속년수 
         SELECT EMPLOYEE_ID AS 사원번호,
                        EMP_NAME AS 사원명,
                        HIRE_DATE AS 입사일,
                        JOB_ID AS 직무코드,
                        TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12)||'년 '|| -- XXX월을 12로 나눈 몫: 년수
                        LPAD(ROUND(MOD(MONTHS_BETWEEN(SYSDATE, HIRE_DATE),12)),3)||'개월'

                                                                                                                       -- XXX월을 12로 나눈 나머지 : 개월 수
            FROM HR.EMPLOYEES
         WHERE DEPARTMENT_ID = 80;

 

 

 

2022.11.18 Oracle SQL 수업