날짜함수

SQL/ORACLE함수 2015. 11. 20. 12:27
728x90
반응형

날짜 함수

문형 사용 예
문형 결과
SYSDATE SYSDATE 99/05/10
SYSTIMESTAMP SYSTIMESTAMP 07/03/07 10:55:24.260425 +09:00
LAST_DAY(날짜값) LAST_DAY('98/02/17') 98/02/28
NEXT_DAY(날짜값, 요일 문자) NEXT_DAY('98/02/17','FRIDAY') 98/02/20
MONTHS_BETWEEN (날짜값1,날짜값2) MONTHS_BETWEEN('97/04/26','95/06/22') 21.1290323
ADD_MONTHS(날짜값, 숫자값) ADD_MONTHS('95/06/22',21) 97/04/22
ROUND(날짜값, 자리수) 현재 날짜가 1999년 5월 10일이라 가정하자.
ROUND(SYSDATE,'MONTH')
99/05/01
TRUNC(날짜값, 자리수) 현재 날짜가 1999년 5월 10일이라 가정하자.
TRUNC(SYSDATE,'YEAR')
99/01/01
EXTRACT(날짜 및 시각 FROM 날짜값) 현재 날짜가 1999년 5월 10일이라 가정하자.
EXTRACT ( YEAR FROM SYSDATE )
1999
TO_YMINTERVAL(문자값) 현재 날짜가 1999년 5월 10일이라 가정하자.
SYSDATE + TO_YMINTERVAL('01-02')
00/07/10
  • SYSDATE에서 시, 분, 초까지 출력하고 싶은 경우는 변환함수를 사용해야 합니다.
    SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
    
  • Oracle10g 부터는 SYSDATE 대신 SYSTIMESTAMP 함수(Database의 TimeZone을 포함한 시스템 날짜)를 이용하면 좀 더 자세히 볼 수 있다.
  • MONTHS_BETWEEN 함수를 쓰는 경우는 큰 날짜를 앞에 써야 양수로 계산됩니다.
  • NEXT_DAY는 입력한 날짜값(DATE) 다음 주의 요일날짜를 보여줍니다.
    요일문자는 LANG 설정에 따라 (월요일..일요일)도 가능하나 MONDAY,TUESDAY 를 추천합니다.

날짜에 대한 산술 연산

연산 결과치 사용목적
날짜 + 숫자 날짜 특정한 날로부터 몇일 후의 날짜 계산
날짜 - 숫자 날짜 특정한 날로부터 몇일 전의 날짜 계산
날짜 - 날짜 숫자 두 날짜사이의 차이를 숫자로 계산
날짜 + 숫자/24 날짜 날짜에 몇 시간 후를 계산
  • 날짜 + 날짜 연산은 불가능하다.
  • 월에 대한 연산은 MONTHS_BETWEEN이나 ADD_MONTHS함수를 통하여 개월차이나 몇 개월 후의 날짜값을 구할 수 있다.
  • 년(Year), 주(Week), 일(Day)등은 따로 제공되는 함수가 없습니다.
    이런 경우에 년차나 특정날짜로부터 며칠 후의 날짜값 등을 계산하고 싶으면 바로 날짜값에 산술연산을 함으로써 가능합니다.

날짜함수에서 사용할 수 있는 포멧

형식 설명
yy 년도를 2자리로
yyyy 년도를 4자리로
year 영어 철자로 표시
mm 월을 표시(01-12)
mon 월을 표시(DEC….):약자로 나온다.
month 월을 표시(1월,2월,….혹은 ,DECEMBER)
d 일을 표시(주에 대한 일)
dd 일을 표시 (월에 대한 일)
ddd 일을 표시 (년에 대한 일)
Q 분기를 구한다.
DAY 요일 (월요일)
DY 요일(월)
HH & HH12 시간을 12시간제로
HH24 24시간제로
MI
SS
AM & PM & A.M & P.M 12시간제 일때 오전 오후를 표시

접미사로 사용하는 포맷

형식 설명 예시
TH 서수로 표시 4 → DD →4
4→ DDTH → 4TH
SP 철자로 표시 4 →DDSP → FOUR
SPTH & THSP 4 → DDSPTH → FOURTH
근무한 년월 수 column “근무년수” Format a 14;

날짜계산

날짜구하기

  1. 특정일(2006-12-22)과 현재 날짜까지의 달의 차이 수(NUMBER 타입으로 변환)
    SQL> select months_between(sysdate,to_date('2006-12-22','yyyy-mm-dd')) from dual;
    
    • months_between(A,B) = A-B/30
  2. 특정일(SYSDATE)에 달수(4)를 더한 날
    SQL> select add_months(sysdate,4)  from dual;
    
  3. 특정일(SYSDATE)의 다음주 요일(friday)
    SQL> select next_day(sysdate,'friday')  from dual;
    
  4. 특정일(SYSDATE)의 해당 월의 마지막 날
    SQL> select last_day(sysdate) from dual;
    
  5. 특정일의 반올림(오후면 다음날..)
    SQL> select round(sysdate,'dd')  from dual;
    
  6. 특정일의 전주 토요일(해당 전주의 마지막 날)에해당하는 날짜
    SQL> select trunc(sysdate,'ww')  from dual;
    
  7. 특정일의 주 일요일(해당 주의 첫째 날)에해당하는 날짜
    SQL> select trunc(sysdate,'D')  from dual;
    

WHERE 조건상의 날짜계산

  1. 어제 날짜칼럼
    BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE-1)+0.99999421
    
  2. 오늘 날짜칼럼
    BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999421
    
  3. 내일 날짜칼럼
    BETWEEN TRUNC(SYSDATE+1) AND TRUNC(SYSDATE+1)+0.99999421
    
  4. 금주 날짜칼럼
    BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')
        AND TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'D')+6.99999421
    
  5. 차주 날짜칼럼
    BETWEEN TRUNC(SYSDATE+8)-TO_CHAR(SYSDATE, 'D')
        AND TRUNC(TRUNC(SYSDATE)+14.99999421)-TO_CHAR(SYSDATE, 'D')
    
  6. 금월 날짜칼럼
    BETWEEN TRUNC(SYSDATE+1)-TO_CHAR(SYSDATE,'DD')
        AND TRUNC(LAST_DAY(SYSDATE))+0.99999421
    
  7. 전월 날짜칼럼
    BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1)+1)-TO_CHAR(SYSDATE,'DD')
        AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))+0.99999421
    
  8. 차월 날짜칼럼
    BETWEEN ADD_MONTHS(TRUNC(SYSDATE),1)-TO_CHAR(SYSDATE,'DD')+1
        AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE),1)+0.99999421)
    

특정일 까지 간격

  • 특정일 까지의 간격을 년, 개월, 일로 표현하기
    SELECT
      TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', 'YYYYMMDD'))/12) "년",
      TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', 'YYYYMMDD')) -
      TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', 'YYYYMMDD'))/12) * 12) "개월",
      TRUNC((MONTHS_BETWEEN(SYSDATE,TO_DATE('20060101', 'YYYYMMDD')) -
      TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20060101', 'YYYYMMDD')))) * 30.5) "일"
    FROM DUAL;
    

당월의 주차

  • 당월의 주차 구하기
    SELECT 
        TO_CHAR(SYSDATE,'YYYYMMDD') AS "날짜"
        ,CEIL((TO_NUMBER(SUBSTRB(TO_CHAR(SYSDATE,'YYYYMMDD'), -2, 2)) + 7 - TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD'),'D')))/7) AS "월별 주차"
    FROM DUAL;
    
    • TO_CHAR(SYSDATE,'YYYYMMDD') 대신 '20070307' 을 넣어서 계산하면 이해가 더 빠르다.

시간 계산 SQL

SELECT TRUNC(TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS')) || ' day ' ||
       TRUNC(MOD((TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS')),1)*24) || ' hour ' ||
       TRUNC(MOD((TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS'))*24,1)*60) || ' minute ' ||
       TRUNC(ROUND(MOD((TO_DATE('20060502223443','YYYYMMDDHH24MISS')-TO_DATE('20060501213344','YYYYMMDDHH24MISS'))*24*60,1)*60)) || ' sec '
       " Time Interval "
FROM DUAL ;
 
dbms/oracle
 
 
 
 

-- 전년도의 12월31일 
SELECT TRUNC(SYSDATE, 'IY')
  FROM DUAL

 

-- 같은년도의 01월01일 
SELECT TRUNC(SYSDATE, 'YY')
  FROM DUAL       
 
-- 분기의 첫월01일
SELECT TRUNC(SYSDATE, 'Q')
  FROM DUAL
 
-- YYYY년 MM월 01일
SELECT TRUNC(SYSDATE, 'MM')
  FROM DUAL

 

-- YYYY년 MM월 DD일
SELECT TRUNC(SYSDATE, 'DD')
  FROM DUAL

 

-- SYSDATE가 속하는 주에서 년도의 1월1일의 요일과 같은 일자  
SELECT TRUNC(SYSDATE, 'WW')
  FROM DUAL

 

-- SYSDATE가 속하는 주에서 월요일의 일자
SELECT TRUNC(SYSDATE, 'IW')
  FROM DUAL
   
-- SYSDATE가 속하는 주에서 월의 1일의 요일과 같은 일자  
SELECT TRUNC(SYSDATE, 'W')
  FROM DUAL
 
-- YYYY년 MM월 DD일 HH:00:00  
SELECT TRUNC(SYSDATE, 'HH') HH,
       TRUNC(SYSDATE, 'HH12') HH12,
       TRUNC(SYSDATE, 'HH24') HH24
  FROM DUAL
 
-- YYYY년 MM월 DD일 HH:MI:00  
SELECT TRUNC(SYSDATE, 'MI') MI
  FROM DUAL


-- 분기산출 Sample 1 

SELECT TRUNC(TO_DATE('20080310', 'YYYYMMDD'), 'Q') "1분기",
       TRUNC(TO_DATE('20080425', 'YYYYMMDD'), 'Q') "2분기",
       TRUNC(TO_DATE('20080802', 'YYYYMMDD'), 'Q') "3분기",
       TRUNC(TO_DATE('20081130', 'YYYYMMDD'), 'Q') "4분기"
  FROM DUAL

 

-- 분기산출 Sample 2 

SELECT YMD,
       Q_YMD,
       DECODE(MM, '01', '1분기', '04', '2분기', '07', '3분기', '10', '4분기') "분기"
  FROM (      
        SELECT ADD_MONTHS(TO_DATE('20080125', 'YYYYMMDD'), LEVEL-1) YMD,
               TRUNC(ADD_MONTHS(TO_DATE('20080110', 'YYYYMMDD'), LEVEL-1), 'Q') Q_YMD,
               TO_CHAR(TRUNC(ADD_MONTHS(TO_DATE('20080110', 'YYYYMMDD'), LEVEL-1), 'Q'), 'MM') MM
          FROM DUAL
        CONNECT BY TO_CHAR(ADD_MONTHS(TO_DATE('20080110', 'YYYYMMDD'), LEVEL-1), 'YYYYMMDD') <= '20081231'
       )
 

출처 : Tong - 병조님의

 

 

 

  • nest_day( date형, 숫자)
select next_day(sysdate,1) from dual
해당 date의 다음 주의 날짜 중 2번째 인자를 요일로 보고
인자에 해당하는 날짜를 출력.
주일을 한주의 첫째날로 인식함.
2번째 인자는 숫자, 문자 다 됨. 숫자같은 경우, 1은 월, 2는 화 … 이런식으로 인식.
문자같은 경우, 현재 사용하는 문자 인코딩이 한글이라 그런지
한글로 '월' '화' 이런식으로 줄 수 있음.

ex) select to_char(next_day(sysdate,5), 'year month dd day') from dual

  • last_day ( date형 )

last_day( date형 ) 해당 년월의 마지막 날 출력

ex)select last_day(sysdate) from dual

 

  • add_months ( date형 )

해당 날짜에 달을 더함.

 

  • to_yminterval('00-00')

의미가 year, month의 interval(간격)을 준다.

앞 00은 year값을, 뒤 00은 month값을 의미.

ex)select hiredate, hiredate+to_yminterval('00-01') from emp

결과

14/01/26 15/02/26

 

 

[출처] 날짜함수|작성자 듀스포에

728x90
반응형
블로그 이미지

nineDeveloper

안녕하세요 현직 개발자 입니다 ~ 빠르게 변화하는 세상에 뒤쳐지지 않도록 우리모두 열심히 공부합시다 ~! 개발공부는 넘나 재미있는 것~!

,