날짜형 함수
년 월 일이 다 있어야 날짜 타입이다.
1) SYSDATE
- 시스템이 제공하는 기본 날짜형
- 년, 월, 일, 시, 분, 초 정보제공
- '+'와 '-' 연산의 대상
- 단순 연산이 아닌 일수를 더하고 뺀다. (윤달까지도 계산)
예)
SELECT SYSDATE, SYSDATE+10, SYSDATE-200 FROM DUAL;
2) ADD_MONTHS(d, n)
- 'd'로 주어진 날짜에서 'n' 월 수를 더한 날짜 반환
예) 회원테이블에서 MEM_MEMORIALDAY 컬럼이 가입일이라고 가정했을 때, 모든 회원의 유효기간이 3개월이며 모두 재등록할 경우 재등록 날자 10일전에 문자데이터를 전송하고자 한다. 각 회원의 문자전송 시작일을 구하시오.
Alias 회원번호, 회원명, 가입일, 종료일, 문자전송일
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MEMORIALDAY AS 가입일,
ADD_MONTHS(MEM_MEMORIALDAY,3) AS 종료일,
ADD_MONTHS(MEM_MEMORIALDAY,3) - 10 AS 문자전송일
FROM MEMBER;
3) NEXT_DAY(d, char)
- 주어진 날짜 'd' 이후 처음 만나는 char요일 날짜
SELECT NEXT_DAY(SYSDATE,'금') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'목요일') FROM DUAL;4) LAST_DAY(d)
- 주어진 날짜 'd'의 월에 해당하는 마지막 일자 반환
SELECT LAST_DAY(SYSDATE), LAST_DAY('2000210') FROM DUAL;5) MONTHS_BETWEEN(d1, d2)
- 두 날짜 자료 'd1'과 'd2' 사이의 개월 수를 반환
SELECT ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), '00010101')) FROM DUAL;6) EXTRACT(fmt FROM d)
-
주어진 날짜데이터 'd'에서 fmt로 정의된 값을 추출하여 반환
-
fmt는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
-
반환 데이터 타입은 숫자형식
-
예) 매입테이블에서 2005년도 월별 매입정보를 조회하시오.
Alias 월, 매입수량, 매입금액
SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
COUNT(*) AS 건수,
SUM(BUY_QTY) AS 매입수량,
SUM(BUY_QTY*BUY_COST) AS 매입금액
FROM BUYPROD
WHERE EXTRACT(YEAR FROM BUY_DATE)=2005
GROUP BY EXTRACT(MONTH FROM BUY_DATE)
ORDER BY 1;
예) 사원테이블에서 이번달에 입사한 사원정보를 조회하시오.
SELECT EMPLOYEE_ID 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
HIRE_DATE AS 입사일,
EMAIL AS 이메일
FROM EMPLOYEES
WHERE EXTRACT(MONTH FROM HIRE_DATE)=EXTRACT(MONTH FROM SYSDATE)
ORDER BY 3;형 변환 함수
1) CAST(expr AS 타입[(크기)]
- 'expr'로 제공되는 자료를 '타입[(크기)]' 형식으로 형변환
- 형변환은 CAST가 사용된 위치에서 일시적 변환
예) 사원테이블에서 부서코드 50에 속한 사원들의 근속년수를 조회하시오.
Alias 사원번호, 사원명, 입사일, 근속년수 (근속년수는 'xx년' 형식의 문자열로 변환 출력하시오.)
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
CAST(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE) AS CHAR(2)) || '년' AS 근속년수
FROM EMPLOYEES
WHERE DEPARTMENT_ID=50;
2) TO_CHAR(char)
- CHAR, CLOB 타입을 VARCHAR2 타입의 문자열로 변환
- TO_CHAR(d [ , fmt] ) : 날짜 자료 'd'를 fmt 형식의 문자열로 변환
- TO_CHAR(n [ , fmt] ) : 숫자 자료 'n'을 fmt 형식의 문자열로 변환
날짜 형식 지정 문자열
| 형식문자열 | 의미 | 사용예 |
|---|---|---|
| AD, BC, CC | 세기를 출력 | TO_CHAR(SYSDATE, 'CC') |
| YYYY,YYY,YY,Y | 년도 출력 | TO_CHAR(SYSDATE,'YY') |
| Q | 분기 | TO_CHAR(SYSDATE, 'YY Q') |
| MONTH, MON, MM, RM | 월 | TO_CHAR(SYSDATE, 'YYYY-MM') |
| W, WW, IW | 주차 | TO_CHAR(SYSDATE,'W') |
| D, DD, DDD, J | 일 | TO_CHAR(SYSDATE,'D') |
| DAY, DY | 주의 요일 | TO_CHAR(SYSDATE,'DAY') |
| HH, HH24, HH12 | 시간(HH와 HH12는 같은 형식) | TO_CHAR(SYSDATE,'HH12') |
| AM, PM, A.M, P.M | 오전, 오후 표현 | TO_CHAR(SYSDATE,'P.M.') |
| MI | 분 | |
| SS, SSSS | 초 | 'SSSS' : 자정이후 경과된 시간을 초로 반환 |
| 기타 | 사용자가 임의로 정한 문자열은 반드시 " "안에 기술 |
(사용형식)
SELECT TO_CHAR(MEM_BIR),
TO_CHAR(MEM_BIR, 'YYYY-MM-DD DAY'
FROM MEMBER;
숫자 형식 지정 문자열
| 형식문자열 | 의미 |
|---|---|
| 9 | 대응되는 위치의 값이 유효한 값이면 데이터를 출력하고 무효의 0은 공백처리 |
| 0 | 대응되는 위치의 값이 유효한 값이면 데이터를 출력하고 무효의 0도 출력 |
| &,L | 화페기호를 출력 |
| MI | 음수 출력인 경우 우측에 '-' 부호 출력 |
| PR | 음수 출력인 경우 '<>' 안에 출력 |
| ,(콤마), .(소숫점) |
예)2005년 2월 제품별 매입현황을 조회하시오. Alias 제품코드, 매입수량, 매입금액
SELECT BUY_PROD AS 제품코드,
TO_CHAR(SUM(BUY_QTY), '99,999') AS 매입수량,
TO_CHAR(SUM(BUY_QTY*BUY_COST),'L99,999,999') AS 매입금액
FROM BUYPROD
WHERE BUY_DATE BETWEEN '20050201' AND LAST_DAY('20050201')
GROUP BY BUY_PROD
ORDER BY 1;
3) TO_NUMBER(c [ , fmt ])
- 숫자형식으로 제공된 문자열을 숫자형으로 변환
- 사용되는 fmt는 TO_CHAR에서 사용하는 형식 저장 문자열과 동일
4) TO_DATE(c [ , fmt ])
- 날짜 형식의 문자열을 날짜 타입으로 변환
- 형식 지정 문자열은 TO_CHAR의 형식 지정 문자열과 동일
예) 2005년 6월 13일 판매일보를 작성하시오
Alias 날짜, 상품코드, 판매수량, 구매자
SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 날짜,
CART_PROD AS 상품코드,
CART_QTY AS 판매수량,
CART_MEMBER AS 구매자
FROM CART
WHERE SUBSTR(CART_NO,1,8)='20050613';
Hello, world!