오라클의 날짜(DATE)

유동현·2022년 10월 10일
Datesql날짜
0

오라클

목록 보기
7/18
post-thumbnail

DATE

  • 오라클에서 날짜 연산의 기본단위는 일(day)이다.
    ex)
SELECT SYSDATE "COL1"   --2022-08-17 11:14:40
       ,SYSDATE+1 "COL2"  --2022-08-18 11:14:40
       ,SYSDATE-2 "COL3" --2022-08-15 11:14:40
       ,SYSDATE+30 "COL4" --2022-09-16 11:14:40
FROM DUAL;
--2022-10-10	2022-10-11	2022-10-08	2022-11-09

  • 즉 그러므로 시간단위의 연산을 위해서는
--○시간 단위 연산
SELECT SYSDATE "COL1"
    ,SYSDATE + 1/24 "COL2"
    ,SYSDATE - 2/24 "COL3"
FROM DUAL;

--○현재 시간과 현재시간 기준 1일 2시간 3분 4초후를 조회

SELECT SYSDATE "현재시간"
       ,SYSDATE+1+2/24+3/(24*60)+4/(24*60*60) "이후"
FROM DUAL;

과 같이 해야한다.

  • TO_DATE()함수를 통해 문자 타입을 날짜 타입으로 변환을 수행하는 과정에서 내부적으로 해당 날짜에 대한 유효성 검사가 이루어진다.

  • ORA-01843: not a valid month (입력한 월이 잘못 되었을 경우)

  • 날짜 타입은 ROUND와 TRUNC를 통해 반올림과 절삭이 가능하다.

--※날짜 데이터를 대상으로 반올림, 절삭 등의 연산을 수행할 수 있다.
--○날짜 반올림
SELECT SYSDATE "COL1"                 --기본 현재 날짜
       ,ROUND(SYSDATE, 'YEAR') "COL2" -- 2023-01-01연도까지 유효한 데이터(상반기/하반기 기준)
       ,ROUND(SYSDATE, 'MONTH') "COL3" -- 2022-09-01월까지 유효한 데이터(15일 기준)
       ,ROUND(SYSDATE, 'DD') "COL4"   --2022-08-18일까지 유효한 데이터(정오 기준)
       ,ROUND(SYSDATE, 'DAY') "COL5"  -- 2022-08-21일까지 유효한 데이터(수요일 정오기준)
FROM DUAL;

--2022-10-10	2023-01-01	2022-10-01	2022-10-11	2022-10-09

--○날짜 절삭
SELECT SYSDATE "COL1"                 --기본 현재 날짜
       ,TRUNC(SYSDATE, 'YEAR') "COL2" -- 2022-01-01연도까지 유효한 데이터
       ,TRUNC(SYSDATE, 'MONTH') "COL3" --2022-08-01 월까지 유효한 데이터
       ,TRUNC(SYSDATE, 'DD') "COL4"   --2022-08-17일까지 유효한 데이터
       ,TRUNC(SYSDATE, 'DAY') "COL5"  -- 2022-08-14그 전 주에 해당하는 일요일
FROM DUAL;

--2022-10-10	2022-01-01	2022-10-01	2022-10-10	2022-10-09

DATE에 관련된 함수들

ADD MONTH

  • 날짜에 월을 더해주는 함수
SELECT SYSDATE "COL1"
      ,ADD_MONTHs(SYSDATE ,2) "COL2"
      ,ADD_MONTHs(SYSDATE ,3) "COL3"
      ,ADD_MONTHs(SYSDATE ,-2) "COL4"
      ,ADD_MONTHs(SYSDATE ,-3) "COL5"
FROM DUAL;
/*
2022-08-17 11:38:15	
2022-10-17 11:38:15
2022-11-17 11:38:15
2022-06-17 11:38:15	
2022-05-17 11:38:15
*/

MONTH BETWEEN

--○MONTHS_BETWEEN()
--첫번째 인자값에서 두번째 인자값을 뺀 개월 수를 반환한다. -> 단위 : 개월 수

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2002-05-31','YYYY-MM-DD')) "COL1"
FROM DUAL;
--242.564126344086021505376344086021505376

-->개월 수의 차이를 반환하는 함수
-- 결과값의 부호가 - 음수 로 반환되었을 경우에는
-- 첫 번째 인자값에 해당하는 날짜보다
-- 두 번째 인자값에 해당하는 날짜가 미래 라는 의미로 확인할 수 있다.

NEXTDAY

  • 첫번째 인자를 기준으로 두번째 인자에 해당하는 날을 구해주는 함수

--○NEXT_DAY()
SELECT NEXT_DAY(SYSDATE,'토') "COL1"
      ,NEXT_DAY(SYSDATE,'월') "COL2"
FROM DUAL;
-->>2022-08-20 11:44:06
--  2022-08-22 11:46:30
-- 다음일요일과 다음 월요일을 구하는것

LAST_DAY

  • 해당 날짜가 포함되어 있는 그 달의 마지막 날을 반환한다
--> 
SELECT LAST_DAY(SYSDATE) "COL1"
    ,  LAST_DAY(TO_DATE('2020-02-10','YYYY-MM-DD')) "COL2"
    ,  LAST_DAY(TO_DATE('2019-02-10','YYYY-MM-DD')) "COL3"    
FROM DUAL;

TO_YMINTERBER & TO_DSINTERVAL

  • YMINTERBER = Year + Month + INTERBER 로 연,월을 date형에 더해주는것
  • DSINTERBER = DAY + Second + INTERBER 로 일 ~ 초까지 date형에 더해줄 수 있다.
--○ 현재 시간을 기준으로 1년 2개월 3일 4시간 4분 6초 후를 조회한다.
-- TO_YMINTERVAL(), TO_DSINTERVAL()
--  연월              일부터 초까지

SELECT SYSDATE "현재시간"
     , SYSDATE + TO_YMINTERVAL('01-02') "연산결과"
     , SYSDATE + TO_DSINTERVAL('003 04:05:06') "연산결과"
FROM DUAL;
 
--==>>     2023-10-17 17:47:49   
--         2022-08-20 21:52:55

응용


--   복무 기간은 22개월로 한다

-- 1. 전역 일자를 구한다.

-- 2. 하루 꼬박꼬박 3끼 식사를 한다고 가정하면
--    태민이가 몇 끼를 먹어야 집에 보내줄까...

SELECT (ADD_MONTHS(SYSDATE,22)-SYSDATE)*3
FROM DUAL;


-- 『1일 2시간 3분 4초』를... 『초』로 환산하면...
SELECT (1일) + (2시간) + (3분) + (4초)
FROM DUAL;

SELECT (1*24*60*60) + (2*60*60) + (3*60) + (4)
FROM DUAL;
--==>>93784

-- 『93784』를... 다시 『일, 시간, 분, 초』로 환산하면...

SELECT  TRUNC(TRUNC(TRUNC(93784/60)/60)/24)"일"
    ,   MOD(TRUNC(TRUNC(93784/60)/60),24)"시간"
    ,   MOD(TRUNC(93784/60),60) "분" --60으로 나누어지지 않은 것은 60분=1시간이 안되는 진짜 분을 의미할것!
    ,   MOD(93784, 60) "초"
FROM DUAL;

--1	2	3	4

위를 통해 특정일까지 남은 시간을 구할 수 있다.

SELECT SYSDATE "현재"
       ,TO_DATE('2023-01-16 18:00:00','YYYY-MM-DD HH24:MI:SS') "수료일"
       ,TRUNC(TO_DATE('2023-01-16 18:00:00','YYYY-MM-DD HH24:MI:SS')-SYSDATE) "일"
       ,TRUNC(MOD((TO_DATE('2023-01-16 18:00:00','YYYY-MM-DD HH24:MI:SS')-SYSDATE)*24,24)) "시"
       ,TRUNC(MOD((TO_DATE('2023-01-16 18:00:00','YYYY-MM-DD HH24:MI:SS')-SYSDATE)*24*60,60)) "분"
       ,TRUNC(MOD((TO_DATE('2023-01-16 18:00:00','YYYY-MM-DD HH24:MI:SS')-SYSDATE)*24*60*60,60)) "초"
FROM DUAL;


--2022-10-10	2023-01-16	97	21	58	25

날짜 타입 변환 가능 함수들

  • ※날짜나 통화 형식이 맞지 않을 경우...
    설정값을 통해 세션을 설정할 수 있다.
ALTER SESSION SET NLS_LANGUAGE = 'ENGLISH';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';
ALTER SESSION SET NLS_CURRENCY = '$'; --￦ 표시
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

TO_CHAR()

  • 숫자나 날짜 데이터를 문자 타입으로 변환시켜주는 함수
--○날짜형 -> 문자형
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "COL1" --2022-08-17
       ,TO_CHAR(SYSDATE, 'YYYY') "COL2"      --2022
       ,TO_CHAR(SYSDATE, 'YEAR') "COL3"     --TWENTY TWENTY-TWO
       ,TO_CHAR(SYSDATE, 'MM') "COL4"       --08
       ,TO_CHAR(SYSDATE, 'MONTH') "COL5"    --AUGUST   
       ,TO_CHAR(SYSDATE, 'MON') "COL6"      --AUG
       ,TO_CHAR(SYSDATE, 'DD') "COL7"       --17
       ,TO_CHAR(SYSDATE, 'MM-DD') "COL8"    --08-17
       ,TO_CHAR(SYSDATE, 'DAY') "COL9"      --WEDNESDAY
       ,TO_CHAR(SYSDATE, 'DY') "COL10"      --WED
       ,TO_CHAR(SYSDATE, 'HH24') "COL11"    --17
       ,TO_CHAR(SYSDATE, 'HH') "COL12"      --05
       ,TO_CHAR(SYSDATE, 'HH AM') "COL13"   --05 PM
       ,TO_CHAR(SYSDATE, 'HH PM') "COL14"   --05 PM
       ,TO_CHAR(SYSDATE, 'MI') "COL15"      --11
       ,TO_CHAR(SYSDATE, 'SS') "COL16"      --46
       ,TO_CHAR(SYSDATE, 'SSSSS') "COL17"   --61906 시간초
       ,TO_CHAR(SYSDATE, 'Q') "COL18"       --3 분기
       FROM DUAL;
  • TO_CHAR는 숫자를 문자로 변환시킬수도 있다.
--숫자형 -> 문자형
SELECT 7 "COL1"
       ,'7' "COL2"
       ,TO_CHAR(7) "COL3"
FROM DUAL;
-->조회결과의 정렬이 어떤지 확인
--숫자는 우측정렬
--문자는 좌측정렬

TO_CHAR 활용법

--○ TO_CHAR() 활용 → 형식 맞춤 표기 결과값 반환
SELECT 60000 "COL1"
     , TO_CHAR(60000, '99,999') "COL2"
     , TO_CHAR(60000, '$99,999') "COL3"
     , TO_CHAR(60000, 'L99,999') "COL4"
     , LTRIM(TO_CHAR(60000, 'L99,999')) "COL5"
FROM DUAL;
--==>> 60000    60,000    $60,000           ￦60,000   ￦60,000

TO_NUMBER()

  • 문자 데이터를 숫자 타입으로 변환시켜주는 함수
--○문자형 -> 숫자형
SELECT TO_NUMBER('4') "COL1"
      ,'4' "COL2"
      ,4 "COL3"
      ,TO_NUMBER('04') "COL4" --숫자로 변환되며 0이 탈락되어 4
FROM DUAL;
--4	4	4	4
--조회 결과가 우측 정렬
  • TO_CHAR와 같이 사용
--○현재 날짜에서 현재 년도(2022)를 숫자 형태로 조회(반환)

SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) "연도(숫자형)"
FROM DUAL;
--조회결과 우측정렬로 숫자형 확인

EXTRACT

--○EXTRACT()

SELECT TO_CHAR(SYSDATE, 'YYYY') "COL1" --2022 ->연도를 추출하여 문자 타입으로..
       ,TO_CHAR(SYSDATE,'MM') "COL2"   --08  -> 월을 추출하여 문자 타입으로...
       ,TO_CHAR(SYSDATE,'DD') "COL3"   --17  -> 일을 추출하여 문자 타입으로..
       ,EXTRACT(YEAR FROM SYSDATE) "COL4" --2022(숫자형)  -> 연도를 추출하여 숫자 타입으로
       ,EXTRACT(MONTH FROM SYSDATE) "COL5"    --8(숫자형) -> 월을 추출하여 숫자 타입으로
       ,EXTRACT(DAY FROM SYSDATE) "COL6"    --17 -> 일을 추출해서 숫자 타입으로..
FROM DUAL;

-->년,월,일 이외의 다른 항목은 불가~!!
profile
유동현
이전 포스트

오라클 내장 함수들

다음 포스트

CASE WHEN , DECODE, 뷰 , 서브쿼리, RANK

0개의 댓글