DB|SQL

[오라클 SQL] 날짜 함수

soyeonisgood 2022. 8. 14. 16:21

날짜 함수

  • 연산 대상이 날짜인 함수
  • 날짜형 데이터 타입 종류
    • DATE, TIMESTAMP, INTERVAR DAY TO SECOND ... 등
    • DATE와 TIMESTAMP를 주로 사용

 

날짜 함수 종류

날짜함수 설명
SYSDATE 시스템의 현재 날짜 및 시간 반환. (DATE형)
CURRENT_DATE session time zone의 현재 날짜를 DATE유형의 그레고리력 값으로 반환
SYSTIMESTAMP 밀리초 포함한 시스템 현재 날짜 및 시간 반환. (TIMESTAMP WITH TIME ZONE 형)
EXTRACT( {YEAR | MONTH | DAY | HOUR | MINUTE |
SECOND ... } )		 날짜 시간 또는 간격 표현식에서 지정된 날짜 시간 필드의 값 추출하여 반환
MONTH_BETWEEN(date1, date2) 두 날짜 사이의 월 수 반환. (date1이 date2보다 빠르면 음수)
ADD_MONTHS(date, integer) 날짜 date에 integer 개월 더함. 기준 되는 날짜가 말일이면 계산된 값도 해당 월의 말일 반환
LAST_DAY(date) date날짜가 포함된 월의 마지막 일자 반환
NEXT_DAY(date, char) date날짜 이후의 char로 이름 지정된 첫 번째 요일 날짜 반환
char은 전체이름, 약어, 숫자 가능 (SUNDAY:1, MONDAY:2 ... )
ROUND(date [,fmt]) fmt에 지정된 단위로 날짜 반올림하여 반환
fmt은 YEAR, MONTH, DAY, DD, HH, HH24, MI 등 가능
TRUNC(date [,fmt]) fmt에 지정된 단위로 날짜 내림

 

TO_DATE()

  • 문자를 날짜로 변환
  • TO_DATE(char [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
    • 주의: TO_DATE('800101', 'RRMMDD') 를 TO_DATE('800101') 변형해도 가능한 경우 있지만 
      시스템에 따라 오류 발생할 수도 있다!
    • YYMMDD와 RRMMDD의 차이점
      • YY: 현재 년도 2000년 기준.
        • YYMMDD,901010 : 2090년
      • RR: 50년 이상 2000년, 50미만
        • RRMMDD,901010 : 1990년
SELECT TO_CHAR(TO_DATE('801010','YYMMDD'), 'YYYY-MM-DD') FROM dual;
SELECT TO_CHAR(TO_DATE('801010','RRMMDD'), 'YYYY-MM-DD') FROM dual;
		-- YY: 현재 년도 기준. 90은 2090
		-- RR: 현재 년도가 2020인 경우 48년은 2048년
				
SELECT TO_CHAR(TO_DATE('901010','YYMMDD'), 'YYYY-MM-DD') FROM dual; -- 20901010
SELECT TO_CHAR(TO_DATE('901010','RRMMDD'), 'YYYY-MM-DD') FROM dual; -- 19901010

SELECT TO_CHAR(TO_DATE('481010','YYMMDD'), 'YYYY-MM-DD') FROM dual;	-- 20481010	
SELECT TO_CHAR(TO_DATE('481010','YYMMDD'), 'YYYY-MM-DD') FROM dual; -- 20481010

 

TO_CHAR()

  • 날짜를 문자로 변환
  • TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD DAY') FROM dual;
			
SELECT TO_CHAR(SYSDATE, 'YYYY MM DD DAY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"') FROM dual; 
			
SELECT name, TO_CHAR(hireDate, 'YYYY"년" MM"월" DD"일"') hireDate
FROM emp;
			
SELECT name, hireDate
FROM emp
WHERE EXTRACT(YEAR FROM hireDate)=2022;
-- 동일
SELECT name, hireDate
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') =2022;
			
SELECT TO_CHAR(SYSDATE, 'MON DD DAY'),  -- 한국식
	TO_CHAR(SYSDATE, 'MON DD DAY', 'NLS_DATE_LANGUAGE=american') en -- 미국식
FROM dual;
			
SELECT 
	TRUNC(SYSDATE) 오늘,
	TO_CHAR(SYSDATE, 'd') 요일_수치,
	TO_CHAR(TRUNC(SYSDATE, 'd'), 'YYYYMMDD') 일요일,
	TO_CHAR(SYSDATE, 'w') 월기준주차
FROM dual;  -- 22/08/05	6	20220731	1

 

날짜형 데이터의 연산 

연산 결과 설명
날짜 + 숫자 날짜 숫자 만큼의 날 수(일수) 날짜에 더함
날짜 - 숫자 날짜 숫자 만큼의 날 수(일수) 날짜에서 뺌
날짜 + 숫자/24(시간) 날짜 숫자 만큼의 시간을 날짜에 더함
날짜1 - 날짜2 일수(날수) 두 날짜 사이의 일 수

 

-- YYYY:년도, MM:월 DD:일, HH24:시간(24시), MI:분, SS:초

-- 시스템 날짜,시간 출력
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')FROM dual;

-- 오늘 날짜부터 100일 후
SELECT TO_DATE('2022-08-14', 'YYYY-MM-DD')+100 FROM dual;

-- 크리스마스 디데이
SELECT TO_DATE('2022-02-25', 'YYYY-MM-DD') - SYSDATE 디데이 FROM dual;
	-- 소수점으로 나오면 TRUNC()로 자르면 된다
    
-- 입사한지 100일이 지나지 않은 사원 
SELECT empNo, name, hireDate, TRUNC(SYSDATE-hireDate) 입사일수
FROM emp
WHERE (SYSDATE-hireDate)<100;
	-- (현재날짜-입사일)
    
-- 10분 전
SELECT TO_CHAR(SYSDATE-10/24/60,'YYYY-MM-DD HH24:MI:SS') FROM dual;
			
-- 10초 전
SELECT TO_CHAR(SYSDATE-10/24/60/60,'YYYY-MM-DD HH24:MI:SS') FROM dual;

 

 

INTERVAL Literals(간격 리터널)

  • INTERVAL Literals(간격 리터널) 이용한 날짜 가감
    • SYSDATE + (INTERVAL '1' YEAR) -- 1년 후 (오늘)
    • SYSDATE + (INTERVAL '1' MONTH) -- 한달 후 (오늘)
    • SYSDATE + (INTERVAL '1' DAY) -- 내일
    • SYSDATE + (INTERVAL '1' HOUR) -- 1시간 후
    • 외에도 HOUR, MINUTE, SECOND 도 가능
-- 2시간 10분 후 
SELECT TO_CHAR(SYSDATE + (INTERVAL '02:10' HOUR TO MINUTE), 'YYYY-MM-DD HH24:MI:SS') FROM dual;
				
-- 근속년수가 1년 미만인 사원. empNo, name, hireDate
SELECT empNo, name, hireDate, TRUNC(SYSDATE-hireDate) 입사일수
FROM emp
WHERE (hireDate + (INTERVAL '1' YEAR)) > SYSDATE;			
		
-- 내년 1월 1일 
SELECT TO_CHAR(TRUNC(SYSDATE + (INTERVAL '1' YEAR), 'YYYY'), 'YYYY-MM-DD HH24:MI:SS')
FROM dual
	-- 2023-01-01 00:00:00
	-- TRUNC(,'YYYY') 로 1년 후 년도만 가져오고
    	-- TO_CHAR(,'YYYY-MM-DD HH24:MI:SS')로 1/1 0시0분0초 세팅

 

 

EXTRACT()

  • EXTRACT( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR } FROM { expr } )
-- 2010년 이후에 입사한 사람 (2010년 포함)
SELECT emp, name, hireDate
FROM emp
WHERE EXTRACT(YEAR FROM hireDate) >= 2010
	-- EXTRACT()로 입사년도 가져오기

 

 

MONTHS_BETWEEN(date1, date2)

  • 두 날짜의 간격 월(달) 수로 반환
-- 간격 예시
SELECT MONTHS_BETWEEN(TO_DATE('2022-05-20', 'YYYY-MM-DD'),
	TO_DATE('2022-04-10', 'YYYY-MM-DD')) FROM dual; -- 1.322...			
SELECT MONTHS_BETWEEN(TO_DATE('2022-05-20', 'YYYY-MM-DD'),
	TO_DATE('2022-04-20', 'YYYY-MM-DD')) FROM dual; -- 1				
SELECT MONTHS_BETWEEN(TO_DATE('2022-05-19', 'YYYY-MM-DD'),
	TO_DATE('2022-04-20', 'YYYY-MM-DD')) FROM dual; -- 0.9....
    
    
-- 근속 년수 구하기
SELECT empNo, name, rrn, hireDate,
	TRUNC(MONTHS_BETWEEN(SYSDATE, hireDate)/12)
FROM emp;

-- 나이 구하기
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-08-08','YYYY-MM-DD'))/12)

-- 주민번호를 이용하여 나이와 성별 구하기
WITH tb AS (
	SELECT empNo, name, rrn,
		DECODE(MOD(SUBSTR(rrn,8,1),2),0,'여자','남자') 성별,
		TO_DATE(
			CASE
				WHEN SUBSTR(rrn,8,1) IN (1,2,5,6) THEN '19'
				WHEN SUBSTR(rrn,8,1) IN (3,4,7,5) THEN '20'
				ELSE '18'
			END||SUBSTR(rrn,1,6), 'YYYYMMDD'
		) birth
	FROM emp
)
SELECT empNo, name, rrn, birth, TO_CHAR(birth,'YYYY-MM-DD) birth,
	성별, TRUNC(MONTHS_BETWEEN(SYSDATE, birth)/12) age
FROM tb;

 

 

ADD_MONTHS(date, integer)

  • 개월 후 날짜 반환
  • (INTERVAL '1' MONTH) 대신 사용하기 좋다.
-- 다음 달
SELECT ADD_MONTHS(SYSDATE, 1) 다음달 FROM dual;
-- 이전 달
SELECT ADD_MONTHS(SYSDATE, -1) 이전달 FROM dual;

-- 6개월 후
SELECT ADD_MONTHS(TO_DATE('20220330', 'YYYYMMDD'), 6),	
	ADD_MONTHS(TO_DATE('20220331', 'YYYYMMDD'), 6)
FROM dual;
	-- 모두 2022-09-30 출력
    	-- (INTERVAL '1' MONTH)은 31일, 30일 연산에서에러 발생 가능성.
        
-- 6개월 이내에 입사한 사람. empNo, name, hireDate
SELECT empNo, name, hireDate
 FROM emp
WHERE ADD_MONTHS(hireDate, 6) > SYSDATE;
		
-- LAST_DAY(date): 달의 마지막 날짜
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM dual;
		 
-- 이번 달은 말일까지 며칠 남았는지? 
SELECT LAST_DAY(SYSDATE)-SYSDATE FROM dual;

 

LAST_DAY(date)

  • 달의 마지막 날짜 반환
SELECT SYSDATE, LAST_DAY(SYSDATE) FROM dual;
		 
-- 이번 달은 말일까지 며칠 남았는지? 
 SELECT LAST_DAY(SYSDATE)-SYSDATE FROM dual;

 

ROUND(date [,fmt])

  • 날짜 반올림
  • 년도는 7월1일 기준으로 반올림
    • 2022년6월30일은 2022년1월1일 
  • 월은 16일 기준으로 반올림
    • 2022년7월15일은 2022년7월1일
SELECT ROUND(TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'YEAR') FROM dual; -- 2008-01-01
SELECT ROUND(TO_DATE('2007-06-10', 'YYYY-MM-DD'), 'YEAR') FROM dual; -- 2007-01-01
		 
SELECT ROUND(TO_DATE('2007-07-20', 'YYYY-MM-DD'), 'MONTH') FROM dual; -- 2007-08-01
SELECT ROUND(TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'MONTH') FROM dual; -- 2007-07-01

 

TRUNC(date [,fmt])

  • 날짜 내림 
  • 디데이 구하기 편하다
    • TRUNC(SYSDATE,'YEAR') : 22/1/1
SELECT TRUNC(TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'YEAR') FROM dual; -- 2007-01-01
SELECT TRUNC(TO_DATE('2007-06-10', 'YYYY-MM-DD'), 'YEAR') FROM dual; -- 2007-01-01

SELECT ROUND(TO_DATE('2007-07-20', 'YYYY-MM-DD'), 'MONTH') FROM dual; -- 2007-07-01
SELECT ROUND(TO_DATE('2007-07-10', 'YYYY-MM-DD'), 'MONTH') FROM dual; -- 2007-07-01

SELECT TRUNC(SYSDATE, 'D') FROM dual; 
	-- D:주를 기준으로 내림 (1인 일요일로)
		 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:SS') FROM dual; -- 2022-08-05 12:09
SELECT TO_CHAR(TRUNC(SYSDATE), 'YYYY-MM-DD HH24:SS') FROM dual; -- 2022-08-05 00:00
	-- 시분초를 모두 0으로
    
-- 올해 1월 1일부터 오늘까지 몇일이 지났는지 ?	
SELECT TRUNC(SYSDATE - TRUNC(SYSDATE,'YEAR')) FROM dual;
	-- TRUNC(SYSDATE,'YEAR') : 22/1/1
    
-- 생일까지 디데이 구하기
-- 올해 생일이 지나지 않은 경우는
SELECT TRUNC(TO_DATE(EXTRACT(YEAR FROM SYSDATE)
	||SUBSTR('2000-10-16',5),'YYYY-MM-DD') - TRUNC(SYSDATE)) 남은날짜 
FROM dual;
   -- 생년월일에서 월,일 가져오기
    -- 시스템날짜에서 년도(올해) 가져오기
    -- 올해+생일월일 붙이기
    -- 시스템날짜로 빼기
-- 올해 생일이 지난 경우도 고려하기위해 CASE-END문 추가
-- sdate: 올해생일 yyyy-mm-dd
CASE
	WHEN TRUNC(SYSDATE) <= sdate THEN sdate - TRUNC(SYSDATE)
	ELSE (sdate + (INTERVAL '1' YEAR)) - TRUNC(SYSDATE)
END 남은일수

 

NEXT_DAY(date, char)

  • char로 주어진 첫번째 요일 구하기
    • '일요일, 'SUN', 'SUNDAY', 1
    • 일요일:1, 월요일:2 ... 토요일:7
-- 오늘을 기준으로 가장 가까운 금요일
SELECT SYSDATE, NEXT_DAY(SYSDATE, '금요일') FROM dual;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 6) FROM dual;
		-- 일요일:1, 토요일:7
						
-- 오늘 날짜, 이번주 일요일, 이번주 토요일
SELECT SYSDATE,
	NEXT_DAY(SYSDATE, 1) - 7 이번주일요일
	NEXT_DAY(SYSDATE-1, 7) 이번주토요일
FROM dual;

 

 

TO_DATE()

  • 문자를 날짜로 변환
  • TO_DATE(char [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
  • 주의: TO_DATE('800101', 'RRMMDD') 를 TO_DATE('800101') 변형해도 가능한 경우 있지만 
    시스템에 따라 오류 발생할 수도 있다!
SELECT TO_CHAR(TO_DATE('801010','YYMMDD'), 'YYYY-MM-DD') FROM dual;
SELECT TO_CHAR(TO_DATE('801010','RRMMDD'), 'YYYY-MM-DD') FROM dual;
		-- YY: 현재 년도 기준. 90은 2090
		-- RR: 현재 년도가 2020인 경우 48년은 2048년
				
SELECT TO_CHAR(TO_DATE('901010','YYMMDD'), 'YYYY-MM-DD') FROM dual; -- 20901010
SELECT TO_CHAR(TO_DATE('901010','RRMMDD'), 'YYYY-MM-DD') FROM dual; -- 19901010

SELECT TO_CHAR(TO_DATE('481010','YYMMDD'), 'YYYY-MM-DD') FROM dual;	-- 20481010	
SELECT TO_CHAR(TO_DATE('481010','YYMMDD'), 'YYYY-MM-DD') FROM dual; -- 20481010

TO_CHAR()

  • 날짜를 문자로 변환
  • TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD DAY') FROM dual;
			
SELECT TO_CHAR(SYSDATE, 'YYYY MM DD DAY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"') FROM dual; 
			
SELECT name, TO_CHAR(hireDate, 'YYYY"년" MM"월" DD"일"') hireDate
FROM emp;
			
SELECT name, hireDate
FROM emp
WHERE EXTRACT(YEAR FROM hireDate)=2022;
-- 동일
SELECT name, hireDate
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') =2022;
			
SELECT TO_CHAR(SYSDATE, 'MON DD DAY'),  -- 한국식
	TO_CHAR(SYSDATE, 'MON DD DAY', 'NLS_DATE_LANGUAGE=american') en -- 미국식
FROM dual;
			
SELECT 
	TRUNC(SYSDATE) 오늘,
	TO_CHAR(SYSDATE, 'd') 요일_수치,
	TO_CHAR(TRUNC(SYSDATE, 'd'), 'YYYYMMDD') 일요일,
	TO_CHAR(SYSDATE, 'w') 월기준주차
FROM dual;  -- 22/08/05	6	20220731	1
저작자표시 비영리 변경금지 (새창열림)