Database_18_날짜 함수_MONTH_BETWEEN, ADD_MONTH, NEXT_DAY, LAST_DAY, EXTRACT

  -- 날짜 함수
        -- MONTH_BETWEEN(date1, date2) : 두 날짜 간의 달 수를 반환합니다.
        -- ADD_MONTH(date, n) : 날짜에 달 수를 더해 반환합니다.
        -- NEXT_DAY(date, 'char') : 지정 날짜 이후로 가까운 지정한 요일에 해당하는 날짜를 반환합니다.
        -- LAST_DAY(date) : 해당하는 달의 마지막 날을 반환합니다.
        -- EXTRACT( [YEAR|MONTH|DAY] from date) : 지정 날짜로부터 지정된 값을 추출하여 반환합니다.
        -- 시간까지 계산되므로 정수가 나오는 경우가 적습니다.


        -- MONTH_BETWEEN(date1, date2)
SELECT last_name, hire_date, MONTHS_BETWEEN(sysdate, hire_date)
FROM employees;
    /*
    LAST_NAME   HIRE_DATE         MONTHS_BETWEEN(sysdate, hire_date)
    ------------------------------------------------------------------------------------------------------------
    King                 02/06/17           252.560785543608124253285543608124253286
    Kochhar           04/09/21           225.431753285543608124253285543608124253
    De Haan          08/01/13           185.689817801672640382317801672640382318
    Hunold             05/01/03           222
    ...
    -- 소수로 출력됩니다.
    -- 한 달이 되지 않는 경우 TRUNC로 버림합니다.
    */

    
            -- 정수로 일한 개월 수 출력
SELECT last_name, hire_date, TRUNC( MONTHS_BETWEEN(sysdate, hire_date),0 )
FROM employees;
    /*
    LAST_NAME   HIRE_DATE   TRUNC( MONTHS_BETWEEN(sysdate, hire_date),0 )
    -----------------------------------------------------------------------------------------------------------
    King                   02/06/17         252
    Kochhar             04/09/21         225
    De Haan            08/01/13         185
    Hunold               05/01/03         222
    ...
    */

    
        -- 10년 이상 일한 직원 정보 조회(개월 수로 비교해야 합니다.)
SELECT last_name, hire_date, TRUNC( MONTHS_BETWEEN(sysdate, hire_date),0 )
FROM employees
WHERE MONTHS_BETWEEN(sysdate, hire_date) >= 120;
    /*
    LAST_NAME   HIRE_DATE   TRUNC( MONTHS_BETWEEN(sysdate, hire_date),0 )
    -------------------------------------------------------------------------------------------------------
    King                   02/06/17         252
    Kochhar             04/09/21         225
    De Haan            08/01/13         185
    Hunold               05/01/03         222
    ...
    */

        -- ADD_MONTH(date, n)
SELECT last_name, hire_date, ADD_MONTHS(hire_date, 6)
FROM employees;
    /*
    LAST_NAME   HIRE_DATE   ADD_MONTHS(HIRE_DATE, 6)
    ------------------------------------------------------------------------------------
    King                 02/06/17     02/12/17
    Kochhar           04/09/21     05/03/21
    De Haan          08/01/13     08/07/13
    Hunold             05/01/03     05/07/03
    ...
    */

    

        -- NEXT_DAY(date, 'char')
SELECT NEXT_DAY(sysdate, '토요일'), NEXT_DAY(sysdate, '토'), NEXT_DAY(sysdate, 7)
FROM dual;
    /*
    NEXT_DAY(sysdate, '토요일')    NEXT_DAY(sysdate, '토')      NEXT_DAY(sysdate, 7)
    ---------------------------------------------------------------------------------------------------------------
        23/07/08                                    23/07/08                               23/07/08
        -- 시스템이 한국이라 한글로 날짜를 작성해야 합니다.
        -- 숫자로 표현도 가능합니다.
            1:일요일 2:월요일 3:화요일 4:수요일 5:목요일 6:금요일 7:토요일
    */

        -- LAST_DAY(date)
SELECT LAST_DAY(sysdate) 
FROM dual;
    /*
    LAST_DAY(sysdate)
    ------------------------------
        23/07/31
    */

    
        -- 급여일이 매월 10일입니다. 첫 급여일 조회
SELECT last_name, hire_date, LAST_DAY(hire_date)+10
FROM employees;
    /*
    LAST_NAME   HIRE_DATE   LAST_DAY(hire_date)+10
    ---------------------------------------------------------------------------
    King               02/06/17           02/07/10
    Kochhar         04/09/21           04/10/10
    De Haan        08/01/13           08/02/10
    Hunold          05/01/03           05/02/10
    ...
    */

        -- EXTRACT( [YEAR|MONTH|DAY] , date)
SELECT EXTRACT(year from  sysdate), EXTRACT(month from sysdate), EXTRACT(day from sysdate)
FROM dual;
    /*
    EXTRACT(year from  ...)     EXTRACT(month from ...)     EXTRACT(day from ...)
    --------------------------------------------------------------------------------------------------------------
        2023                                           7                                                3
    */

        -- 입사 6개월 후 직무 능력 검사일, 교육 시작일, 첫 급여일 
        -- ( 근무기간은 버림 적용 > 정수, 교육 시작일은 입사 후 월요일, 첫 급여일 10일)
SELECT last_name, 
            TRUNC(MONTHS_BETWEEN(sysdate, hire_date)) AS workingday,
            ADD_MONTHS(hire_date, 6) AS test,
            NEXT_DAY(hire_date, '월요일') AS start_study,
            LAST_DAY(hire_date)+10 AS pay
FROM employees;
    /*
    LAST_NAME     WORKINGDAY    TEST        START_STUDY         PAY
    -----------------------------------------------------------------------------------------------
    King                         252             02/12/17        02/06/24           02/07/10
    Kochhar                   225             05/03/21        04/09/27           04/10/10
    De Haan                  185             08/07/13        08/01/14           08/02/10
    Hunold                     222             05/07/03        05/01/10           05/02/10
    ...
    */