[DB] 예시와 함께 보는 SQL 날짜 함수 총 정리!

SQL 날짜함수 - ctrl + F를 활용하여 원하는 함수를 찾아보세요! 종류 SYSDATE CURRENT_DATE CURRENT_TIMESTAMP ADD_MONTHS LAST_DAY NEXT_DAY ROUND TRUNC + 날짜 포맷팅 관련

1. SYSDATE

SYSDATE 함수는 현재 날짜와 시간을 반환해 주는 함수로 정말 자주 사용하는 함수입니다. 날짜출력 형식 설정에 따라 날짜만 출력되거나 날짜 시간 둘 다 출력될 수 있습니다. 회원가입을 할 때 가입일자를 저장하는 경우가 많은데 이럴 경우 회원가입을 할 때 현재 날짜와 시간을 SYSDATE 함수를 통해 INSERT 할 수 있습니다.  

# 사용예시
SELECT SYSDATE FROM DUAL;  -- 결과 : 24/06/07

# 회원가입 INSERT 쿼리 예시
INSERT INTO MEMBER (
    NAME, 
    TELNO, 
    JOIN_DATE
) 
VALUES (
    '신짱구', 
    '010-1234-1234', 
    SYSDATE
);

---

2. CURRENT_DATE

CURRENT_DATE 함수도 현재 날짜를 반환해 주는 함수입니다. 위에서 설명한 SYSDATE와 사용방법은 같지만 반환값에 차이가 있습니다. 

 

SYSDATE:

• 반환하는 값: 데이터베이스 서버의 현재 날짜와 시간.
• 시간대: 데이터베이스 서버의 로컬 시간대.
• 주로 사용: 서버의 현재 시간을 기준으로 작업이 수행되는 경우.

 

CURRENT_DATE:

• 반환하는 값: 세션의 현재 날짜와 시간.
• 시간대: 사용자의 세션 시간대 (사용자가 세션을 통해 설정한 시간대).
• 주로 사용: 사용자의 로컬 시간을 기준으로 작업이 수행되는 경우.

# 사용예시
SELECT CURRENT_DATE FROM DUAL;  -- 결과 : 2024/06/07

 

---

3. CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 함수는 현재의 타임스탬프 값을 반환합니다. 

# 사용예시
SELECT CURRENT_TIMESTAMP FROM DUAL;  -- 결과 : 24/06/07 21:53:41.139000000 ASIA/SEOUL

---

4. ADD_MONTHS(날짜,  n)

ADD_MONTHS 함수는 날짜에서 n개월 후 날짜를 반환합니다. 이용권이나 멤버십 등 기간제 상품을 구매하는 경우 구매일로부터 n개월이 지난날을 계산하여 종료일 등을 표시할 수 있을 것 같습니다. 날짜형식을 직접 넣을 수도 있지만 SYSDATE를 넣어도 같은 결괏값이 나옵니다.

# 사용예시
SELECT ADD_MONTHS('2024/06/07', 3) FROM DUAL;  -- 결과 : 24/09/07
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;       -- 결과 : 24/09/07

---

5. LAST_DAY(날짜)

LAST_DAY 함수는 주어진 월의 마지막 날짜를 반환하는 함수입니다. 아래의 쿼리에서는 SYSDATE에 2024/06/07 이었기 때문에 6월의 마지막 날인 6월 30일이 반환되었습니다.

# 사용예시
SELECT LAST_DAY(SYSDATE) FROM DUAL;  -- 결과 : 24/06/30

---

6. NEXT_DAY(날짜, n)

NEXT_DAY 함수는 주어진 날짜 이후 지정된 요일의 첫 번째 날짜를 반환하는 함수입니다. n에 들어올 수 있는 숫자는 1 ~ 7까지 입니다.

 

※ 1 : 일요일, 2 : 월요일, ..., 7 : 토요일

 

아래에 쿼리를 보면 제가 쿼리를 조회한 날짜가 6월 7일 금요일이었습니다.  파라미터에 1이 들어가면 가장 가까운 일요일을 찾는다는 의미이고 7을 넣었을 때에는 다음인 날인 토요일이 가장 가까운 날이어서 아래와 같이 쿼리가 조회됩니다.

# 사용예시
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;  -- 결과 : 2024/06/09
SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;  -- 결과 : 2024/06/10
SELECT NEXT_DAY(SYSDATE, 3) FROM DUAL;  -- 결과 : 2024/06/11
SELECT NEXT_DAY(SYSDATE, 4) FROM DUAL;  -- 결과 : 2024/06/12
SELECT NEXT_DAY(SYSDATE, 5) FROM DUAL;  -- 결과 : 2024/06/13
SELECT NEXT_DAY(SYSDATE, 6) FROM DUAL;  -- 결과 : 2024/06/14
SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL;  -- 결과 : 2024/06/08
SELECT NEXT_DAY(SYSDATE, 8) FROM DUAL;  -- 결과 : 에러발생

만약 1 ~ 7 이외에 다른 숫자를 넣게 되면 에러가 발생합니다.

ORA-01846: not a valid day of the week
01846. 00000 -  "not a valid day of the week"

---

7. ROUND(날짜, 자리수) & TRUNC(날짜, 자리수)

ROUND 함수는 날짜를 반올림하고  TRUNC 함수는 날짜를 버림 해주는 함수입니다.

6월 7일에 월을 기준으로 반올림과 버림을 했기 때문에 같은 결과가 나온 것을 볼 수 있습니다.

# 사용예시
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL;  -- 결과 : 24/06/01 
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;  -- 결과 : 24/06/01

---

마지막으로 날짜 포맷팅 관련입니다. 저는 SQL 편집 툴로 SQL DEVELOPER를 사용했는데요. 혹시 날짜 형식 변경을 원하시는 분은 [ 도구 ] - [ 환경설정 ] - [ 데이터베이스 ] - [ NLS ] 경로에서 날짜 형식 확인 및 수정이 가능합니다. 

 

날짜 포맷은 다음과 같습니다.

-- YYYY     : 4자리 연도로 표시   
-- YY          : 끝의 2자리 연도로 표시  
-- YEAR     : 연도를 알파벳으로 표시  

-- MM        : 달을 숫자로 표시  
-- MON      : 달을 알파벳 약어로 표시  
-- MONTH : 달을 알파벳으로 표시  

-- DD         : 일자를 숫자로 표시  
-- DAY       : 일에 해당하는 요일   
-- DY         : 일에 해당하는 요일의 약어  

 
-- HH         : 12시간으로 표시(1-12)  
-- HH24     : 24시간으로 표시(0-23)  

-- MI          : 분을 표시   
-- SS          : 초를 표시   

-- AM, PM  : 오전, 오후 

 

TO_CHAR를 활용하여 날짜를 원하는 형식으로 포맷팅 하여 조회할 수 있습니다. 바로 활용법으로 보겠습니다.

# 사용예시
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;  -- 결과 : 2024-06-08 00:09:51
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS') FROM DUAL;    -- 결과 : 20240608 00:09:58