[SQL] 데이터 타입 변환 함수 - to_date(), to_char(), to_number()

 

 

SQL문을 사용시 데이터가 저장된 타입에 따라서 컬럼들을

WHERE, GROUP BY에 사용하기 위해 형변환 함수를 사용해야 할 때가 종종 있다.

 

오늘은 형변환시 자주 사용하는 함수들에 대해서 정리해보겠다.

 

 

to_date()

• 문자를 날짜형(date)으로 변환하는 함수이다.
• to_date(문자(날짜),'날짜모델요소')

 

SELECT to_date('20231012','yyyymmdd')
FROM dual;

SELECT to_date('2023-10-12 13:45:30','yyyy-mm-dd hh24:mi:ss')
FROM dual;

 

SELECT *
FROM hr.employees
-- 연도 4자리 / 월 / 일
-- 날짜는 꼭 to_date () 형변환 함수 사용 하도록 ★★
WHERE hire_date BETWEEN to_date('2006/01/01','yyyy/mm/dd') AND to_date('2006/12/31','yyyy/mm/dd');

 

 

 

 

 

to_char()

• 문자형 변환 함수
• 오버로딩(overloading): 함수 이름만 같을 뿐 다른 함수

함수 기능	함수 사용 형태
날짜 변환	to_char(날짜, '날짜모델형식')
숫자 변환	to_char(숫자, '숫자모델형식')

 

 

1. 날짜형을 문자형으로 변환

SELECT sysdate
    , to_char(sysdate, 'yyyymmdd') AS "yyyymmdd"
    , to_char(sysdate, 'yyyy-mm-dd') AS "yyyy-mm-dd"
    , to_char(sysdate, 'yyyy-mm-dd hh24:mi.ss.sssss') "yyyy-mm-dd hh24:mi.ss.sssss"
FROM dual;


SELECT sysdate
    , to_char(sysdate, 'yyyy yy rr rrrr year')
    , to_char(sysdate, 'yyyy')||'년'
    , to_char(sysdate, 'yyyy"년"') -- "" 사용 주의
FROM dual;    


SELECT to_char(add_months(sysdate,-1), 'month mon fmmm') -- fm 선행되는 제로를 제거하는 옵션
FROM dual;


SELECT to_char(sysdate, 'ddd dd d') -- d 일요일 1 ~ 토요일 7
     , to_char(sysdate, 'day dy')   -- 요일 (언어 종속)
     , to_char(sysdate, 'ww w')     -- 주
     , to_char(sysdate, 'q"분기"') 
     , to_char(sysdate, 'dd ddth ddthsp') -- 세기, 21 21st twenty-first
FROM dual;

SELECT to_char(sysdate, 'hh hh12 hh24 am pm')
FROM dual;

 

 

2. 숫자형을 문자형으로 변환

• number(숫자형)을 char(문자형)으로 변환하는 함수
• 음수 표현 가능
• 자릿수 주의
  • 0: 해당 자릿수까지 맞춰서 모두 출력
  • 9: 해당 자릿수까지 있으면 출력하고 그렇지 않으면 출력하지 않는다.

SELECT salary
     , to_char(salary,'$999,999')
     , to_char(salary,'$000,999')
     , to_char(salary,'$000,999.00')
     , to_char(salary,'l000,999.00') -- l(소문자 L) 요소는 지역통화부호를 출력
FROM hr.employees;

 

-- #### 
-- 자릿수 잘못 잡힘
SELECT to_char(-1000,'9999')
FROM dual;

SELECT to_char(-1000,'9999')
     , to_char(-1000,'9999pr') -- 음수일 경우 <>로 묶는다, <1000>
     , to_char(-1000,'9999mi') -- 음수 부호 뒤에 표현, 1000-
     , to_char(1000,'s9999')   -- 양수(+) 부호 표현
FROM dual;

 

 

3. 지역에 맞게 통화 가치 구분

• g: 천단위 구분
• d: 소수점 구분

SELECT to_char(hire_date,'month mon day dy')
     , to_char(salary,'l999g999d00')
FROM hr.employees;

 

 

(참고) 

ALTER SESSION SET NLS_LANGUAGE = 'simplified chinese'; -- 언어 변경
ALTER SESSION SET NLS_TERRITORY = china;               -- 국가 변경

-- 언어/국가 변경 확인
SELECT *
FROM nls_session_parameters;

 

 

 

to_number()

• 문자형(숫자 문자일 경우에만!)을 숫자형으로 변환하는 함수
• to_number(문자(숫자),'숫자모델요소'(생략))

-- 두개 다 같은 쿼리임
SELECT 1 + to_number('2','9')
FROM dual;

SELECT 1 + to_number('2')
FROM dual;

 

 

 

---