[불친절한SQL] 기초쿼리, 날짜 함수

○ 날짜 함수

날짜 값(date, timestamp, interval)을 조작하는 함수이다.

예제를 위해 NLS 파라미터를 아래와 같이 설정했다.

ALTER SESSION SET NLS_DATE_FORMAT	  = "YYYY-MM-DD HH24:MI:SS";
ALTER SESSION SET NLS_TIMESTAMP_FORMAT 	  = "YYYY-MM-DD HH24:MI:SS.FF";
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF TZH:TZM";

 

SYSDATE 함수

초(second)가 포함된 데이터베이스 서버의 날짜 값을 DATE타입으로 반환한다.

SELECT SYSDATE FROM DUAL;

 

SYSDATE 함수

소수점 이하 초() 포함된 데이터베이스 서버의 날짜 값을 TIMESTAMP WITH TIME ZONE 타입으로 반환한다.

SELECT SYSTIMESTAMP FROM DUAL;

 

NEXT_DAY 함수

date 이후 char에 지정된 요일에 해당하는 가장 가까운 날짜 값을 반환한다.

NEXT_DAY(date, char)

NLS_LANGUAGE 파라미터 설정에 영향을 받기 때문에 숫자 형식의 요일로 지정하는 편이 바람직하다.

일	월	화	수	목	금	토
1	2	3	4	5	6	7

SELECT NEXT_DAY(DATE '2022-11-29', 'MON') AS c1
     , NEXT_DAY(DATE '2022-11-29',     2) AS c2
  FROM DUAL;

 

LAST_DAY 함수

date가 속한 월의 월말일을 반환한다.

SELECT LAST_DAY(DATE '2025-02-15') AS c1 FROM DUAL;

 

ADD_MONTHS 함수

date에서 integer의 개월 수를 연산한 날짜 값을 반환한다.

date가 해당 월의 말일이면, c2와 c3같이 열말일로 연산된다.

 

월말일을 고려하지 않고 개월 수를 연산하기 위해서는 사용자 정의 함수를 사용해야한다.

ADD_MONTHS(date, integer)

SELECT ADD_MONTHS(DATE '2050-01-15', -1) AS c1
     , ADD_MONTHS(DATE '2050-01-31',  1) AS c2
     , ADD_MONTHS(DATE '2050-02-28',  1) AS c3
  FROM DUAL;

 

MONTHS_BETWEEN 함수

date1과 date2 사이의 개월 수를 반환한다.

 

date1, date2의 일자(day)가 같거나, 월말일이면 정수를,

그렇지 않으면 일자 수를 31로 나눈 값을 반환한다.

date2가 date1보다 크면 음수를 반환한다.

SELECT MONTHS_BETWEEN(DATE '2050-04-15', DATE '2050-01-15') AS c1
     , MONTHS_BETWEEN(DATE '2050-04-30', DATE '2050-01-31') AS c2
     , MONTHS_BETWEEN(DATE '2050-04-30', DATE '2050-01-15') AS c3
     , MONTHS_BETWEEN(DATE '2050-01-15', DATE '2050-04-30') AS c4
  FROM DUAL;

아래 쿼리는 2000년 기준의 근속 연수를 계산한다.

SELECT ename, TRUNC(MONTHS_BETWEEN(DATE '2000-01-01', hiredate) / 12) AS c1
  FROM emp;

 

EXTRACT 함수

expr에서 날짜 정보를 추출한다.

EXTRACT({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM expr)

WITH w1 AS (SELECT TIMESTAMP '2050-01-02 12:34:56.789' AS dt FROM DUAL)
SELECT EXTRACT(YEAR    FROM dt) AS c1, EXTRACT(MONTH   FROM dt) AS c2
     , EXTRACT(DAY     FROM dt) AS c3, EXTRACT(HOUR    FROM dt) AS c4
     , EXTRACT(MINUTE  FROM dt) AS c5, EXTRACT(SECOND  FROM dt) AS c6
  FROM w1;

 

ROUND 함수

fmt를 기준으로 date를 반올림한다. fmt의 기본값은 DD다.

fmt은 포맷 요소(format element)를 통해 더 다양하게 사용 가능하다.

ROUND(date [, fmt])

WITH w1 AS (SELECT TO_DATE('2051-08-16 12:31:31', 'YYYY-MM-DD HH24:MI:SS') AS dt FROM DUAL)
SELECT TRUNC(dt) AS c1, TRUNC(dt, 'DD') as c2 FROM w1;

 

TRUNC 함수

fmt를 기준으로 date를 버린다. fmt의 ROUND함수와 동일하다.

TRUNC(date [, fmt])

WITH w1 AS (SELECT TO_DATE('2051-08-16 12:31:31', 'YYYY-MM-DD HH24:MI:SS') AS dt FROM DUAL)
SELECT ROUND(dt) AS c1, ROUND(dt, 'DD') as c2 FROM w1;