[함수] TRUNC 잘라버리기
TRUNC 소숫점, 날짜등 잘라버리는데 사용
형식 : TRUNC( "값", "옵션" )
기본적인 결과는 날짜의 경우 시간이하 0으로, 숫자의 경우 소숫점이하를 잘라버린다.
WITH TEMP AS (
SELECT
SYSDATE AS DT
, 1234.56 AS NUM
FROM DUAL
)
SELECT
TO_CHAR(DT, 'YYYY/MM/DD HH24:MI:SS') AS DT1
, TO_CHAR(TRUNC(DT), 'YYYY/MM/DD HH24:MI:SS') AS DT2
, NUM AS NO1
, TRUNC(NUM) AS NO2
FROM TEMP;
날짜를 구분해서 셋팅할 경우
달, 월, 일, 시간, 분, 초 이하를 구분해 0으로 셋팅가능
옵션에 설정한 값 미만을 초기화 한다.
WITH TEMP AS (
SELECT TO_DATE('2019/11/23 13:34:44', 'YYYY/MM/DD HH24:MI:SS') AS DT FROM DUAL
)
SELECT
TO_CHAR(DT, 'YYYY/MM/DD HH24:MI:SS') AS 원본
, TO_CHAR(TRUNC(DT, 'YEAR'), 'YYYY/MM/DD HH24:MI:SS') AS YEAR
, TO_CHAR(TRUNC(DT, 'MONTH'), 'YYYY/MM/DD HH24:MI:SS') AS MONTH
, TO_CHAR(TRUNC(DT, 'DAY'), 'YYYY/MM/DD HH24:MI:SS') AS DAY
, TO_CHAR(TRUNC(DT, 'DD'), 'YYYY/MM/DD HH24:MI:SS') AS DD
, TO_CHAR(TRUNC(DT, 'HH24'), 'YYYY/MM/DD HH24:MI:SS') AS HH24
, TO_CHAR(TRUNC(DT, 'MI'), 'YYYY/MM/DD HH24:MI:SS') AS MI
FROM TEMP;
소숫점을 구분해서 셋팅할 경우
WITH TEMP AS (
SELECT 1234.56 NUM FROM DUAL
)
SELECT
NUM
, TRUNC(NUM, -3) AS M3
, TRUNC(NUM, -2) AS M2
, TRUNC(NUM, -1) AS M1
, TRUNC(NUM, 0) AS ZER
, TRUNC(NUM, 1) AS P1
, TRUNC(NUM, 2) AS P2
FROM TEMP;
