Day052

TO_CHAR : 날짜 -> 문자로 형변환

TO_CHAR(날짜, '원하는 형태')

현재 시스템의 시간 -> 가입일, 퇴직일, 등록일, 저장일, 수정일 등으로 활용될 수 있다.

SELECT SYSDATE
FROM dual;

SELECT MONTHS_BETWEEN('23/02/01', '23/01/01')
,MONTHS_BETWEEN('23-02-01', '23-01-01')
--한달 이내인 경우인지 확인
,MONTHS_BETWEEN(SYSDATE, '23-03-14')
FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
    ,TO_CHAR(SYSDATE, 'YYYYMMDD')
    ,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS')
    ,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
    ,TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM dual;

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2)
FROM dual;

SELECT NEXT_DAY(SYSDATE, '수')
FROM dual;

SELECT LAST_DAY(SYSDATE)
-- 2023-01-05 ~ 2023-01-31
, '2023-01-05 ~ ' || TO_CHAR( LAST_DAY('2023-01-05'), 'YYYY-MM-DD' )
FROM dual;

SELECT
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
    , TO_CHAR(ROUND(SYSDATE), 'YYYY-MM-DD HH:MI:SS')
    , TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD HH:MI:SS')
FROM dual;

--다음달의 첫날
SELECT LAST_DAY(SYSDATE)+1
FROM dual;
--이번달의 첫날
SELECT TO_CHAR(SYSDATE, 'YY/MM/') || '01'
    --월 기준으로 버림
    ,TRUNC(SYSDATE, 'MM')
FROM dual;
--이번달 마지막날
SELECT LAST_DAY(SYSDATE)
FROM dual;
--전달 마지막날
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1))
    ,TRUNC(SYSDATE, 'MM')-1
FROM dual;

--TO_CHAR
--TO_DATE
--TO_NUMBER
--이번달, 특정 달의 첫날
SELECT TO_CHAR(SYSDATE, 'YY/MM/') || '01'
    , TRUNC(SYSDATE, 'MM')
    , TO_DATE('2022-10-05') --2022년 10월의 첫날
    , TRUNC(TO_DATE('2022-10-05'), 'MM')
    , TO_DATE('2022-03-05') + 13 --2023년 03월 5일 + 13일 이후
FROM dual;

SELECT studno, name, TO_CHAR(birthday, 'YYYY-MM-DD') BIRTHDAY
FROM student
WHERE TO_CHAR(birthday, 'MM') = '01';

SELECT empno, ename, TO_CHAR(hiredate, 'YYYY/MM/DD') HIREDATE
FROM emp
--WHERE TO_CHAR(hiredate, 'MM') <= '03';
WHERE TO_CHAR(hiredate, 'MM') IN ('01', '02', '03');
--WHERE TO_CHAR(hiredate, 'MM') BETWEEN '01' AND '03';

SELECT ename, sal, comm, TO_CHAR(sal*12+comm, '999,999') SALARY
FROM emp
WHERE ename = 'ALLEN';

SELECT name, pay, bonus, TO_CHAR(pay*12+bonus, '999,999') TOTAL
FROM professor
WHERE deptno = 201;

SELECT empno, ename, TO_CHAR(hiredate, 'YYYY-MM-DD') HIREDATE
    , TO_CHAR(sal*12+comm, '$99999') SAL
    , TO_CHAR( (sal*12+comm)  * 1.15, '$99999') "15% UP"
FROM emp
WHERE comm IS NOT NULL;

SELECT profno, name, pay, NVL(bonus, 0) AS bonus
    , TO_CHAR(pay*12+NVL(bonus, 0), '999,999') TOTAL
FROM professor
WHERE deptno = 201;

SELECT empno, ename, sal, comm, NVL2(comm, sal+comm, sal*0) NVL2
FROM emp
WHERE deptno = 30;

SELECT empno, ename, comm, NVL2(comm, 'Exist', 'NULL') NVL2
FROM emp
WHERE deptno = 30;

SELECT deptno, name, DECODE(deptno, 101, 'Computer Engineering', 'ETC') 학과명
FROM professor;

SELECT deptno, name
    , DECODE(deptno, 101, 'Computer Engineering'
    , 102, 'Multimedia Engineering'
    , 103, 'SoftwareEngineering'
    , 'ETC' ) 학과명
FROM professor;

SELECT name, deptno
    , DECODE(deptno, 101, DECODE(name, 'Audie Murphy', 'BEST!', 'GOOD!'), 'N/A') RESULT
FROM professor;

SELECT name, jumin, DECODE(SUBSTR(jumin, 7, 1) , 1, '남자', '여자') Gender
FROM student
WHERE deptno1 = 101;

SELECT name, tel
    , DECODE( SUBSTR(tel, 1, INSTR(tel, ')')-1 )
    , 02, '서울', 031, '경기', 055, '경남', 051, '부산', 052, '울산', '기타') LOC
FROM student;
--WHERE deptno1 = 101;

SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, ')')-1 ) 지역번호
    ,CASE( SUBSTR(tel, 1, INSTR(tel, ')')-1 ) )
        WHEN '02' THEN '서울'
        WHEN '031' THEN '경기'
        WHEN '051' THEN '부산'
        WHEN '055' THEN '울산'
        ELSE 'ETC' 
    END 지역명
FROM student
WHERE deptno1 = 201;

SELECT name, birthday
, CASE WHEN TO_CHAR(birthday, 'MM') IN (01, 02, 03) THEN '1분기'
    WHEN TO_CHAR(birthday, 'MM') IN (04, 05, 06) THEN '2분기'
    WHEN TO_CHAR(birthday, 'MM') IN (07, 08, 09) THEN '3분기'
    WHEN TO_CHAR(birthday, 'MM') IN (10, 11, 12) THEN '4분기'
    END 분기
    , CEIL(TO_NUMBER( TO_CHAR(birthday, 'MM') ) / 3) || '분기' 분기
FROM student;

SELECT empno, ename, sal
    , CASE WHEN sal BETWEEN 1 AND 1000 THEN 'Level 1'
        WHEN sal BETWEEN 1001 AND 2000 THEN 'Level 2'        
        WHEN sal BETWEEN 2001 AND 3000 THEN 'Level 3'
        WHEN sal BETWEEN 3001 AND 4000 THEN 'Level 4'
        ELSE 'Level 5'
    END "LEVEL"
    , 'Level ' || CEIL( sal / 1000 ) 급여등급
FROM emp
ORDER BY sal DESC;

SELECT *
FROM t_reg;