[Oracle] 형변환 함수

💡형변환 함수

---

• to_char(숫자): 숫자를 문자로 변환
• to_char(날짜): 날짜를 문자로 변환
• to_number(문자): 문자를 숫자로 변환
• to_date(문자): 문자를 날짜로 변환

 

숫자형과 문자형, 무엇이 다를까?

SELECT
    weight,
    to_char(weight),
    length(to_char(weight)), --같은 구문
    length(weight), --같은 구문
    substr(weight, 1, 1),
    weight || 'kg'
FROM tblcomedian;

숫자형인 경우 왼쪽 상단에 '123', 문자형인 경우 'ABC'가 있는 것을 확인할 수 있다.

그리고 숫자는 우측 정렬, 문자는 좌측 정렬이 되어 있다.

length가 암시적인 형변환으로 숫자형인 weight를 문자형으로 변환하여 문자열의 길이를 출력한다.

 

Grid Colorize data types 설정

Grid 설정에서 Colorize data types를 체크해주면 데이터 형식에 따라 다른 색으로 확인할 수 있다.

 

💡to_char(숫자)

---

to_char(숫자 [, 형식문자열])

 

형식 문자열 구성요소

• a. 9: 숫자 1개를 문자 1개로 바꾸는 역할을 한다. 빈 자리를 스페이스로 치환한다.
• b. 0: 숫자 1개를 문자 1개로 바꾸는 역할을 한다. 빈 자리를 0으로 치환한다.
• c. $: 통화 기호 표현
• d. L: 통화 기호 표현(Locale)
• e. .: 소숫점
• f. ,: 천단위 표기

 

a. 9

SELECT
    weight,
    '@' || to_char(weight) || '@',
    '@' || to_char(weight, '99999') || '@', --@    64@
    '@' || to_char(-weight, '99999') || '@' --@   -64@
FROM tblcomedian;

'9'는 숫자 1개를 문자 1개로 바꾸는 역할을 한다. 빈 자리를 스페이스로 치환한다.

자릿수를 지정한 문자열을 만들 때 사용한다.

 

b. 0

'0'은 숫자 1개를 문자 1개로 바꾸는 역할을 한다. 빈 자리를 0으로 치환한다.

 

c. $

'$'는 통화 기호 표현이다.

 

d. L

SELECT
    100,
    '$' || 100,
    to_char(100, '$999'),
    --to_char(100, '999달러')
    100 || '달러',
    to_char(100, 'L999')
FROM dual;

'L'은 통화 기호 표현이다. '$'와 다르게 Locale을 따른다.

 

e. .

'.'은 소숫점을 표기한다.

 

f. ,

SELECT 
    1234567.89,
    to_char(1234567.89, '9,999,999.9'), --$,d
    ltrim(to_char(567.89, '9,999,999.9')),
    to_char(3414153136.89, '9,999,999.9') --출력되지 않음
FROM dual;

','은 천단위를 표기한다.

길이가 표현할 수 없을 경우 출력되지 않기 때문에 발생할 수 있는 최대 길이로 정해주는 게 좋다.

 

💡to_char(날짜)

---

형식문자열 구성요소

• a. yyyy: 년(4자리)
• b. yy: 년(2자리)
• c. month: 월(풀네임)
• d. mon: 월(약어)
• e. mm: 월(2자리)
• f. day: 요일(풀네임)
• g. dy: 요일(약어)
• h. ddd: 일(올해의 며칠)
• i. dd: 일(이번달의 며칠)
• j. d: 일(이번주의 며칠) == 요일(숫자)
• k. hh: 시(12시)
• l. hh24: 시(24시)
• m. mi: 분
• n. ss: 초
• o. am(pm): 오후/오후(am == pm)

 

SELECT to_char(sysdate, 'yyyy') FROM dual; --2023
SELECT to_char(sysdate, 'yy') FROM dual; --23
SELECT to_char(sysdate, 'month') FROM dual; --8월(Agust)
SELECT to_char(sysdate, 'mon') FROM dual; --8월(Aug)
SELECT to_char(sysdate, 'mm') FROM dual; --08
SELECT to_char(sysdate, 'day') FROM dual; --화요일
SELECT to_char(sysdate, 'dy') FROM dual; --화
SELECT to_char(sysdate, 'ddd') FROM dual; --241
SELECT to_char(sysdate, 'dd') FROM dual; --29
SELECT to_char(sysdate, 'd') FROM dual; --3
SELECT to_char(sysdate, 'hh') FROM dual; --02
SELECT to_char(sysdate, 'hh24') FROM dual; --14
SELECT to_char(sysdate, 'mi') FROM dual; --43
SELECT to_char(sysdate, 'ss') FROM dual; --26
SELECT to_char(sysdate, 'am') FROM dual; --오후
SELECT to_char(sysdate, 'am') FROM dual; --오후

 

자주 사용하는 형식문자

아래의 형식 문자들은 자주 사용되기 때문에 기억해 두는 게 좋다.

 

날짜형 출력

SELECT 
    sysdate, --2023-08-29 14:48:54.000
    to_char(sysdate, 'yyyy-mm-dd'), --2023-08-29
    to_char(sysdate, 'hh24:mi:ss'), --14:48:54
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), --2023-08-29 14:48:54
    to_char(sysdate, 'day am hh:mi:ss') --화요일 오후 02:48:54
FROM dual;

 

입사구분

SELECT
    name,
    to_char(ibsadate, 'yyyy-mm-dd') AS ibsadate,
    to_char(ibsadate, 'day') AS DAY,
    CASE
        WHEN to_char(ibsadate, 'd') IN ('1', '7') THEN '휴일입사'
        ELSE '평일입사'
    END AS 입사구분
FROM tblinsa;

 

요일별 입사한 인원수

SELECT 
    count(CASE
        WHEN to_char(ibsadate, 'd') = '1' THEN 1
    END) AS 일요일,
    count(decode(to_char(ibsadate, 'd'), '2', 1)) AS 월요일,
    count(decode(to_char(ibsadate, 'd'), '3', 1)) AS 화요일,
    count(decode(to_char(ibsadate, 'd'), '4', 1)) AS 수요일,
    count(decode(to_char(ibsadate, 'd'), '5', 1)) AS 목요일,
    count(decode(to_char(ibsadate, 'd'), '6', 1)) AS 금요일,
    count(decode(to_char(ibsadate, 'd'), '7', 1)) AS 토요일
FROM tblinsa;

상품 구매 날짜를 출력할 경우에 어떤 상품이 주로 어떤 요일에 많이 팔렸는지에 대한 마케팅 정보가 될 수 있다.

 

2000년에 입사한 인원

SELECT * FROM TBLINSA
    WHERE ibsadate >= '2000-01-01' AND ibsadate <= '2000-12-31'; --오답

SELECT * FROM TBLINSA
    WHERE ibsadate >= '2000-01-01 00:00:00' AND ibsadate <= '2000-12-31 23:59:59'; --오답

'2000-01-01'에는 년월일 표기는 있지만 시분초 표현이 없다. 그렇다면 날짜/시간이라고 말할 수 있을까?
시간은 년월일시분초 표현이 있어야만 명확하게 날짜/시간이라고 말할 수 있다.

SELECT * FROM TBLINSA 
    WHERE to_char(ibsadate, 'yyyy') = '2000'; --정답

시분초을 명시하지 않으면 무조건 자정이다. (0시 0분 0초)
2000년 12월 31일 11시 59분 59초가 아니라 0시 0분 0초로 하루 정도가 날아가 버린다.
2000이라는 커다란 데이터가 맞을 때에만 사용한 경우이지만, yyyy를 쓰는 게 더 확실한 표현이 된다.

 

💡to_number(문자)

---

SELECT
    '123',
    '123' * 2, --암시적 형변환
    to_number('123') * 2
FROM dual;

to_number를 사용하지 않더라도 SQL에서는 암시적인 형변환이 되기 때문에 잘 사용하지 않는다.

 

💡to_date(문자)

---

to_date는 문자를 날짜로 바꿀 때 사용한다.

 

SELECT 
    '2023-08-29',                           --2023-08-29(문자형)
    to_date('2023-08-29'),                  --2023-08-29 00:00:00.000
    to_date('2023-08-29', 'yyyy-mm-dd'),    --2023-08-29 00:00:00.000
    to_date('20230829'),                    --2023-08-29 00:00:00.000
    to_date('20230829', 'yyyymmdd'),        --2023-08-29 00:00:00.000
    to_date('2023/08/29'),                  --2023-08-29 00:00:00.000
    to_date('2023/08/29', 'yyyy/mm/dd'),    --2023-08-29 00:00:00.000
    --to_date('2023년08월29일', '2023년mm월dd일') --한글X
    to_date('2023-08-29 15:28:39', 'yyyy-mm-dd hh24:mi:ss') --2023-08-29 15:28:39.000
FROM dual;

날짜가 될 상황이 아니면 날짜형이 아니라 문자형으로 암시적 형변환이 된다.

한글이 들어간 문자는 날짜형으로 변환되지 않는다.

 

SELECT * FROM TBLINSA 
    WHERE ibsadate >= to_date('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
        AND ibsadate <= to_date('2000-12-31 11:59:59', 'yyyy-mm-dd hh24:mi:ss');

위에서 2000년에 입사한 인원의 데이터를 구할 때 yyyy만을 사용했는데, 정확하게 출력하려면 위와 같이 to_date를 사용해야 한다.

 

💡기타 함수

---

날짜/시간 함수

SELECT sysdate FROM dual;

 

날짜 연산 함수

시각 - 시각 = 시간

SELECT
    name,
    ibsadate,
    --sysdate - ibsadate, AS 근무일수--9088.653506944444444444444444444444444444
    round(sysdate - ibsadate) AS 근무일수, --9089
    --round((sysdate - ibsadate) / 365) AS 근무년수, --사용X(윤년 계산 안됨)
    round((sysdate - ibsadate) * 24) AS 근무시수,
    round((sysdate - ibsadate) * 24 * 60) AS 근무분수,
    round((sysdate - ibsadate) * 24 * 60 * 60) AS 근무초수
FROM tblinsa;

날짜 - 날짜 = 시간(일)이다.
즉 9088.6은 입사한 지 9088일하고도 0.6만큼 하루의 절반 정도 지났다는 걸 의미한다.

 

SELECT
    title,
    adddate,
    completedate,
    round((completedate - adddate) * 24) AS 실행하기까지걸린시간
FROM tbltodo
    WHERE completedate IS NOT null
        ORDER BY round((completedate - adddate) * 24) DESC;

TODO 데이터의 완료한 일들 중에서 오래 걸린 순서대로 출력한다.

 

시각 + 시간 = 시각

연산자로 연산

SELECT
    sysdate,
    sysdate + 100 AS "100일 후",
    sysdate - 100 AS "100일 전",
    sysdate + (3 / 24) AS "3시간 후",
    sysdate - (5 / 24) AS "5시간 전",
    sysdate + (30 / 60 / 24) AS "30분 뒤"
FROM dual;

 

시각 - 시간 = 시각

함수로 연산

months_between, add_months 함수를 지원한다.

number months_between(date, date)

SELECT
    name,
    round(sysdate - ibsadate) AS "근무일수",
    --round((sysdate - ibsadate) / 30) AS "근무월수", --사용X
    round(months_between(sysdate, ibsadate)) AS "근무월수",
    round(months_between(sysdate, ibsadate) / 12) AS "근무년수"
FROM tblinsa;

 

SELECT
    sysdate,
    add_months(sysdate, 3), --3달 후
    add_months(sysdate, -3), --2달 전
    add_months(sysdate, 5*12) --5년 뒤
FROM dual;

월단위를 지원하므로 년단위 연산도 가능하다.

 

결론

시각 - 시각을 할 때
1. 일, 시, 분, 초: 연산자(-)를 사용한다.
2. 월, 년: months_between()을 사용한다.
    
시각 +- 시간을 할 때
1. 일, 시, 분, 초: 연산자(+, -)를 사용한다.
2. 월, 년: add_months()를 사용한다.

 

last_day()

SELECT 
    sysdate,
    last_day(sysdate)
FROM dual;

last_day 함수는 해당 날짜가 포함된 마지막 날짜를 반환한다.

해당월이 며칠까지 있는지 알아볼 때 사용한다.