SYSDATE()
SELECT SYSDATE FROM 테이블;
SELECT SYSDATE FROM DUAL;
MONTHS_BETWEEN(date1, date2)
SELECT FIRST_NAME, LAST_NAME, MONTHS_BETWEEN(sysdate, hire_date) FROM EMPLOYEES WHERE DEPARTMENT_ID = 50;
FIRST_NAME | LAST_NAME | MONTHS_BETWEEN(SYSDATE, HIRE_DATE) |
---|
Donald | OConnell | 92.4792387246117... |
Douglas | Grant | 85.737303240740... |
Matthew | Weiss | 127.576012918160... |
ADD_MONTHS()
SELECT ADD_MONTHS (sysdate, 7) FROM DUAL;
ADD_MONTHS(SYSDATE, 7) |
---|
20/05/26 |
NEXT_DAY()
SELECT NEXT_DAY(sysdate, 'sunday') FROM DUAL;
NEXT_DAY(SYSDATE,'SUNDAY') |
---|
20/05/24 |
LAST_DAY()
SELECT LAST_DAY(sysdate) FROM DUAL;
LAST_DAY(SYSDATE) |
---|
20/05/31 |
TO_CHAR()
SELECT TO_CHAR(sysdate, 'yyyy-mm-dd') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DD') |
---|
2020-05-19 |
TO_DATE()
SELECT TO_DATE('2020/05/19', 'YYYY/MM/DD') FROM DUAL;
TO_DATE('2020/05/19','YYYY/MM/DD') |
---|
20/05/19 |
NVL()
SELECT FIRST_NAME, LAST_NAME, NVL(COMMISSION_PCT, 0) COMMISSION FROM EMPLOYEES;
FIRST_NAME | LAST_NAME | COMMISSION |
---|
Alexander | Khoo | 0 |
Shelli | Baida | 0 |
Guy | Himuro | 0 |
DECODE()
SELECT DEPARTMENT_ID, DECODE(DEPARTMENT_ID, 20, '마케팅부', 60, '전산부', 90, '경영부', '부서') FROM EMPLOYEES;
DEPARTMENT_ID | DECODE(DEPARTMENT_ID, 20, '마케팅부', 60, '전산부', 90, '경영부', '부서') |
---|
20 | 마케팅부 |
60 | 전산부 |
90 | 경영부 |
100 | 부서 |
CASE()
SELECT FIRST_NAME, DEPARTMENT_ID,
CASE WHEN DEPARTMENT_ID = 20 THEN '마케팅부'
WHEN DEPARTMENT_ID = 60 THEN '전산실'
WHEN DEPARTMENT_ID = 90 THEN '경영부'
ELSE ''
END '부서명'
FROM EMPLOYEES;
FIRST_NAME | DEPARTMENT_ID | 부서명 |
---|
Donald | 50 | (NULL) |
Douglas | 50 | (NULL) |
Jennifer | 10 | (NULL) |
Michael | 20 | 마케팅부 |
Pat | 20 | 마케팅부 |
Alexander | 60 | 전산실 |
Bruce | 60 | 전산실 |
Diana | 90 | 경영부 |
Reference