[Oracle] 날짜 함수, 타입 변환

묵시적인 데이터 타입 변환

오라클에서 데이터 타입을 내부적으로 변환하기도 함.

WHERE A=B 에서 데이터 타입이 다른 경우 동일한 데이터 타입으로 내부적 변환이 이루어짐.

내부적 변환으로 인해 검색 속도가 저하될 수 있음.

 

A의 데이터 타입	B의 데이터 타입	변환 결과
NUMBER	VARCHAR2 또는 CHAR	B를 NUMBER 타입으로 변환
VARCHAR2 또는 CHAR	NUMBER	A를 NUMBER 타입으로 변환

-> NUMBER 타입으로 맞춰진다고 보면 된다.

 

아래의 쿼리문을 보자.

SELECT studno, name
    FROM student
    WHERE studno = '10102';

studno의 칼럼은 NUMBER 타입인데

문자 타입 '10102'와 비교를 한다. 이때 오라클 내부에서는 아래의 쿼리로 묵시적인 변환이 일어난다.

SELECT studno, name
    FROM student
    WHERE studno = TO_NUMBER('10102');

 

아래의 쿼리문을 보자.

SELECT studno, name
    FROM student
    WHERE grade = 4;

grade 칼럼의 데이터 타입은 VARCHAR2 타입인데 상수값(숫자)와 비교를 하는 연산이 있다.

이때 grade 칼럼의 데이터 타입을 NUMBER로 묵시적인 변환이 발생하여 아래의 쿼리문이 된다.

SELECT studno, name
    FROM student
    WHERE TO_NUMBER(grade) = 4;

 

명시적인 데이터 타입 변환

사용자가 데이터 타입 변환 함수를 이용하여 명시적으로 데이터 타입을 변환하는것.

 

종류	의미
TO_CHAR	숫자/날짜 -> 문자
TO_NUMBER	문자열 -> 숫자
TO_DATE	문자열 -> 날짜

 

TO_CHAR 함수

오라클의 날짜 표준 형식 'YY/MM/DD'

별도 출력 형식 지정하지 않으면 표준 형식이다.

TO_CHAR (number | date, 'format')

 

날짜 출력 형식

종류	의미	예	결과
SCC 또는 CC	세기 표시	(05/11/25, 'CC')	21
YYYY	년도 표시	(05/11/25, 'YYYY')	2005
YY	"	(05/11/25, 'YY')	05
BC 또는 AC	BC, AD 표현	(05/11/25, 'AD')	AD
Q	월을 분기로 표현	(05/11/25, 'Q')	4(10-12월)
MM	월을 숫자로 표현	(05/11/25, 'MM')	11
MONTH	월을 알파벳으로 표현	(05/11/25, 'MONTH')	NOVEMBER
MON	"	(05/11/25, 'MON')	NOV
RM	월을 로마문자로	(05/11/25, 'RM')	XI
WW	연을 주 단위로	(05/11/25, 'WW')	44
W	월을 주 단위로	(05/11/25, 'W')	4
DDD	연중 일로 표현	(05/11/25, 'DDD')	329
DD	일로 표현	(05/11/25, 'DD')	25
D	주중 일로 표현	(05/11/25, 'D')	6 ( 1~7로 표시 일요일=1 )
DY	일에 해당하는 요일 약어	(05/11/25, 'DY')	FRI
DAY	일에 해당하는 요일	(05/11/25, 'DAY')	FRIDAY

 

- 현재 날짜에 대해 세기, 연도, 요일을 출력

SELECT SYSDATE, TO_CHAR(SYSDATE, 'CC'), TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'DAY')
    FROM dual;

 

- 전인하 학생의 학번과 생년월일중에서 년월만 출력

SELECT studno, TO_CHAR(birthdate, 'YY-MM')
    FROM student
    WHERE name='전인하';

 

- 102번 학과 학생의 이름, 학년, 생년월일을 출력 ( ex, 일요일 June 06, 1982 ) <- 06은 며칠을 나타냄

SELECT name, grade, TO_CHAR(birthdate, 'DAY MONTH DD, YYYY')
    FROM student
    WHERE deptno=102;

 

시간 표현 형식

종류	의미
AM 또는 PM	오전(AM)과 오후(PM) 시각 표시
A.M 또는 P.M	오전(A.M)과 오후(P.M) 시각 표시
HH 또는 HH12	시각(1-12)
HH24	24시각(0-23)
MI	분
SS	초

 

- 101번 학과 교수의 이름과 입사일을 출력 ( ex, JUNE 24, 1982 00:00:00 오전 )

SELECT name, TO_CHAR(hiredate, 'MONTH DD, YYYY HH24:MI:SS PM')
    FROM professor
    WHERE deptno=101;

 

기타 날짜 표현

날짜 출력 형식에 하이픈(-), 슬래시(/), 콤마(,) 와 같은 특수 문자 포함 가능

종류	의미
"text"	결과와 함께 출력할 문자열을 인용 부호로 표시
TH	서수로 표시
SP	숫자(기수)를 영문으로 표시
SPTH 또는 THSP	서수를 영문으로 표시

 

- 101번 학과 교수들의 이름, 직급, 입사일을 출력 ( ex, JUN the 24TH of 1982 ) <- 24는 며칠

SELECT name, position, TO_CHAR(hiredate, 'MON "the" DDTH "of" YYYY')
    FROM professor
    WHERE deptno=101;

 

숫자를 문자 형식으로 변환 ( TO_CHAR 함수 )

 

숫자의 출력 형식

종류	의미	예	결과
9	한자리의 숫자 표시	(1234, '99999')	1234
0	앞부분을 0으로 표시	(1234, '099999')	001234
$	달러 기호를 앞에 표시	(1234, '$99999')	$1234
.	소수점을 표시	(1234, '99999.99')	1234.00

 

- 보직 수당을 받는 교수의 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉으로 출력

SELECT name, TO_CHAR((sal+comm)*12, '9,999') "연봉"
    FROM professor
    WHERE comm IS NOT NULL;

 

TO_NUMBER 함수

숫자로 구성된 문자열을 숫자 데이터로 변환

TO_NUMBER(char)

 

- 문자 '1'을 숫자로 출력

SELECT TO_NUMBER('1')
    FROM dual;

 

TO_DATE 함수

숫자와 문자로 구성된 문자열을 날짜 데이터로 변환

TO_DATE(char, 'format')

 

- 교수 테이블에서 입사일이 'june 01, 01'인 교수의 이름과 입사일을 출력 ( MONTH DD YY이다. )

SELECT name, hiredate
    FROM professor
    WHERE hiredate = TO_DATE('june 01, 01', 'MONTH DD, YY');

지정한 월이 부적합하다고 한다.

 

hiredate를 MONTH 형식으로 출력해보자.

SELECT TO_CHAR(hiredate, 'MONTH')
    FROM professor;

 

형식이 한글로 되어있어 X월이라고 나온다.

그래서 아래의 쿼리로 작성해야 한다.

SELECT name, hiredate
	FROM professor
    WHERE hiredate = TO_DATE('6월 01, 01', 'MONTH DD, YY');

 

날짜 함수

날짜 데이터 타입에 사용하는 함수

날짜 데이터를 입력하여 날짜 또는 기간을 결과 값으로 반환하는 함수

종류	의미	결과
SYSDATE	시스템의 현재 날짜	날짜
MONTHS_BETWEEN	날짜와 날짜사이의 개월을 계산	숫자
ADD_MONTHS	날짜에 개월을 더한 날짜 계산	날짜
NEXT_DAY	날짜후의 첫 요일의 날짜를 계산	날짜
LAST_DAY	월의 마지막 날짜를 계산	날짜
ROUND	날짜를 반올림	날짜
TRUNC	날짜를 절삭	날짜

 

 

기본적으로 날짜 데이터에 연산이 가능하다.

종류	결과	의미
날짜 + 숫자	날짜	날짜에 일수를 가산하여 날짜 계산
날짜 - 숫자	날짜	날짜에 일수를 감산하여 날짜 계산
날짜 - 날짜	일수	날짜와 날짜를 감산하여 일수 계산

 

- 교수 번호 9908인 교수의 입사일을 기준으로 입사 30일 후, 60일 후의 날짜를 출력

SELECT name, hiredate, hiredate+30, hiredate+60
    FROM professor
    WHERE profno=9908;

 

SYSDATE 함수

시스템에 저장된 현재 날짜를 반환하는 함수, 초 단위까지 반환

 

- 시스템의 현재 날짜를 출력하라

SELECT SYSDATE
    FROM dual;
    
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS')
    FROM dual;

(결과 1)

(결과 2)

 

MONTHS_BETWEEN, ADD_MONTHS 함수

월 단위로 날짜 연산을 하는 함수

MONTHS_BETWEEN : 날짜와 날짜 사이의 개월 수를 출력

ADD_MONTHS : 특정 개월 수를 더한 날짜를 출력

MONTHS_BETWEEN(date1, date2)
ADD_MONTHS(date, 개월 수)

 

- 1.입사한지 1200개월 미만인 교수의 입사일,

  2. 입사일로부터 현재일까지의 개월 수,

  3. 입사일에서 6개월 후의 날짜를 출력

SELECT hiredate, MONTHS_BETWEEN(SYSDATE, hiredate), ADD_MONTHS(hiredate, 6)
    FROM professor
    WHERE MONTHS_BETWEEN(SYSDATE, hiredate) < 1200;

 

LAST_DAY, NEXT_DAY 함수

LAST_DAY : 해당 날짜가 속한 달의 마지막 날짜를 반환

NEXT_DAY : 해당 일을 기준으로 명시된 요일의 다음 날짜를 반환 ( date 날짜 이후의 첫 번째 'day' 요일의 날짜를 계산)

LAST_DAY(date)
NEXT_DAT(date, 'day')

 

- 오늘이 속한 달의 마지막 날짜와 다가오는 토요일의 날짜를 출력

SELECT LAST_DAY(SYSDATE), NEXT_DAY(SYSDATE, '토')
    FROM dual;

 

ROUND, TRUNC 함수

ROUND 함수

• 일을 반올림 : 정오(낮 12시)를 넘으면 다음날 자정을 출력, 넘지 않으면 그날 자정을 출력
• 월을 반올림 : 15일 이상이면 다음 달 1일을 출력, 넘지 않으면 그 달 1일을 출력
• 년을 반올림 : 6월을 넘으면 다음 해 1월 1일을 출력, 넘지 않으면 그 해 1월 1일을 출력

TRUNC 함수

• 일을 절삭 : 그날 자정을 출력
• 월을 절살 : 그 달 1일 출력
• 년을 절삭 : 그 해 1월 1일 출력

ROUND(date [, 'format'])
TRUNC(date [, 'format'])

 

- 시간 정보를 생략하는 경우 ROUND, TRUNC 함수의 결과

SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS') "기본값",
       TO_CHAR(TRUNC(SYSDATE), 'YY/MM/DD HH24:MI:SS') "절삭",
       TO_CHAR(ROUND(SYSDATE), 'YY/MM/DD HH24:MI:SS') "반올림"
    FROM dual;

 

- 101번 학과 교수의 입사일을 일, 월, 년을 기준으로 반올림하여 출력

SELECT TO_CHAR(hiredate, 'YY/MM/DD HH24:MI:SS'),
       TO_CHAR(ROUND(hiredate, 'dd'), 'YY/MM/DD'),
       TO_CHAR(ROUND(hiredate, 'MM'), 'YY/MM/DD'),
       TO_CHAR(ROUND(hiredate, 'YY'), 'YY/MM/DD')
    FROM professor
    WHERE deptno=101;