오라클(SQL)날짜함수(SYSDATE, NEXT_DAY(D,C), LAST_DAY(D), EXTRACT(FMT,FROM D))

날짜함수(SYSDATE, NEXT_DAY(D,C),  LAST_DAY(D), EXTRACT(FMT,FROM D))

SYSDATE

테이블 다운로드

    -시스템에서 제공하는 날짜(년,월,일) 및 시간정보(시,분,초)를 반환
    -뎃셈과 뺄셈의 대상이 된다. 연산결과는 날수 또는 날짜 자료

사용예)

SELECT SYSDATE+25, SYSDATE-25,TRUNC(SYSDATE)-TO_DATE('20200223')
  FROM DUAL;

ADD_MONTHS(D,N)
    -주어진 날짜데이터 D에 N개월을 더한 날짜 반환

 

SELECT ADD_MONTHS(SYSDATE,10)+10 FROM DUAL;

NEXT_DAY(D,C)
    -주어진 날짜 데이터 D이후 처음 만나는(가장 빠른) C요일의 날짜 반환
    -C는 '월요일',또는 '월' 등으로 기술(MONDAY)등은 사용불가 -> 현재의 환경설정 하에서

사용예)

       SELECT NEXT_DAY(SYSDATE,'월'),
              NEXT_DAY(SYSDATE,'일요일'),
              NEXT_DAY(SYSDATE,'화'),
              NEXT_DAY(SYSDATE,'목'),
              NEXT_DAY(SYSDATE,'금')
         FROM DUAL;

.LAST_DAY(D)
    -주어진 날짜 데이터D에서 해당 월의 마지막 날짜 반환
(사용예)

SELECT LAST_DAY(TO_DATE('20070210'))FROM DUAL;

.EXTRACT(FMT,FROM D)
    -주어진 날짜데이터 D에서 FMT로 정의된 결과를 반환
    -FMT는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 이며 숫자로 취급

사용예)회원테이블에서  회원들의 생년월일을 기준으로 각 월별로  생일날
      축하 문자를 전송하려한다.
      오늘 보내야할 회원목록을 출력하시오
      ALIAS는 회원번호, 회원명, 생일, 핸드폰번호, 이메일주소

생성)
SELECT MEM_ID AS 회원번호, 
       MEM_NAME AS 회원명, 
       MEM_BIR AS 생일, 
       MEM_HP AS 핸드폰번호, 
       MEM_MAIL AS 이메일주소
  FROM MEMBER
 WHERE EXTRACT(MONTH FROM TO_DATE('20210115'))=EXTRACT(MONTH FROM MEM_BIR)
   AND EXTRACT(DAY FROM SYSDATE)=EXTRACT(DAY FROM MEM_BIR);
   
UPDATE MEMBER
   SET MEM_BIR=TO_DATE(TO_CHAR(EXTRACT(YEAR FROM MEM_BIR))||
               TRIM(TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'00'))||
               TRIM(TO_CHAR(EXTRACT(DAY FROM SYSDATE),'00')))
 WHERE MEM_ID IN('q001','w001');
 

쿼리실행
SELECT MEM_ID AS 회원번호, 
       MEM_NAME AS 회원명, 
       MEM_BIR AS 생일, 
       MEM_HP AS 핸드폰번호, 
       MEM_MAIL AS 이메일주소
  FROM MEMBER
 WHERE EXTRACT(MONTH FROM SYSDATE)=EXTRACT(MONTH FROM MEM_BIR);