오라클 - 날짜관련 계산하기(1)

[참고] 관련포스트

오라클 SQL - 함수( 집계함수, 숫자 함수, 문자열함수, 날짜 함수, 형변환 함수, 기타 함수)

1
2
3
4
5
6

SELECT SYSDATE now, SYSDATE+30/(24*60*60) now_plus_30_secs FROM dual;

-- 원하는 날짜 형식으로 출력하는 쿼리문
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') NOW,
TO_CHAR(SYSDATE+30/(24*60*60),'YYYY-MM-DD HH24:MI:SS') NOW_PLUS_30_SECS
FROM DUAL;

1
2
3
4
5
6
7
8
9

SELECT SYSDATE NOW, SYSDATE+1/24 , SYSDATE +1/1440 , SYSDATE + 1/86400 FROM DUAL;

-- 원하는 날짜 형식으로 출력하는 쿼리문
SELECT
    TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'), --NOW
    TO_CHAR(SYSDATE+1/24, 'YYYY-MM-DD HH24:MI:SS'), --One hour from now
    TO_CHAR(SYSDATE +1/1440, 'YYYY-MM-DD HH24:MI:SS'), --1 minutes from now
    TO_CHAR(SYSDATE + 1/86400,'YYYY-MM-DD HH24:MI:SS') -- 1 seconds from now
FROM DUAL;

* 날짜 관련 계산하기 표현

Description	Date Expression
Now	SYSDATE
Tomorrow/ next day	SYSDATE + 1
Seve days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE)+1)
The next Monday, Wendesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY'' ), NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

[추가]

[코드]

Colored By Color Scripter™

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

-- 날짜 함수
-- 현재 시간을 얻는 함수

SELECT SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP
FROM DUAL;

--세션 시간과 포맷 변경
ALTER SESSION SET TIME_ZONE = '-1:0'; -- 한국의 타임존 '09:00'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YY/MM/DD';

-- 날짜 추출함수 EXTRACT*YEAR/MONTH/DAY/HOUR/MINUTE/SECOND FROM ..)
SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (MONTH FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (DAY FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (HOUR FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (MINUTE FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (SECOND FROM SYSDATE) FROM DUAL;

SELECT EXTRACT (HOUR FROM SYSTIMESTAMP) FROM DUAL;
SELECT EXTRACT (MINUTE FROM SYSTIMESTAMP) FROM DUAL;
SELECT EXTRACT (SECOND FROM SYSTIMESTAMP) FROM DUAL;

-- 가입 회원주에 비수기 (2,3,11,12) 월 달에 가입한 회원을 조회하시오.

SELECT * FROM EXAM_MEMBERS
WHERE EXTRACT(MONTH FROM REGDATE) IN (2,3,11,12);

-- 날짜를 누적하는 함수 ADD_MONTH(날짜, 정수)
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;

-- 가입회원 중에 가입한지 6개월이 안되는 회원을 조회하시오
SELECT * FROM EXAM_MEMBERS WHERE REGDATE > ADD_MONTHS(SYSDATE, -6);

-- 날짜의 차이를 알아내는 함수 MONTHS_BETWEEN(날짜, 날짜)
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2013-12-25')) FROM DUAL;

-- 가입회원 중에 가입한지 6개월이 안되는 회원을 조회하시오
SELECT MONTHS_BETWEEN(SYSDATE, REGDATE) FROM EXAM_MEMBERS;
SELECT * FROM EXAM_MEMBERS WHERE MONTHS_BETWEEN(SYSDATE, REGDATE) <6;

-- 다음 요일을 알려주는 함수 NEXT_DAY(현재날짜, 다음요일)
SELECT NEXT_DAY(SYSDATE,'토요일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'토') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,7) FROM DUAL; -- 일:1 ~ 토:7

-- 월의 마지막 일자를 알려주는 함수 LAST_DAY(날짜)
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,1)) FROM DUAL; --다음달 마지막일

-- 지정된 범위에서 날짜를 반올림하는 / 자르는 함수 ROUND/TRUNC(날짜 , 포맷)
SELECT ROUND(SYSDATE, 'CC'), TRUNC(SYSDATE,'CC') FROM DUAL; -- 세기 , 100년다위
SELECT ROUND(SYSDATE, 'YEAR'), TRUNC(SYSDATE,'YEAR') FROM DUAL; --년단위
SELECT ROUND(SYSDATE,'Q'), TRUNC(SYSDATE,'Q') FROM DUAL; --분기
SELECT ROUND(SYSDATE, 'MONTH'), TRUNC(SYSDATE,'MONTH') FROM DUAL; --월단위
SELECT ROUND(SYSDATE, 'W'), TRUNC(SYSDATE,'W') FROM DUAL; --주단위
SELECT ROUND(SYSDATE, 'DAY'), TRUNC(SYSDATE,'DAY') FROM DUAL; --일단위
SELECT ROUND(SYSDATE, 'D'), TRUNC(SYSDATE,'D') FROM DUAL;
SELECT ROUND(SYSDATE, 'HH'), TRUNC(SYSDATE,'HH') FROM DUAL;
SELECT ROUND(SYSDATE, 'MI'), TRUNC(SYSDATE,'MI') FROM DUAL;

SELECT SYSDATE, ROUND(TO_DATE('2052-02-03'), 'CC'), TRUNC(TO_DATE('2051-02-03'), 'CC') FROM DUAL;
SELECT SYSDATE, ROUND(TO_DATE('2045-02-03'), 'CC'), TRUNC(TO_DATE('2045-02-03'), 'CC') FROM DUAL;

-- 지정된 범위에서 날짜를 반올림하는 / 자르는 함수 ROUND/TRUNC(날짜 , 포맷) 출력하기 좋게 쿼리 변경.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SELECT N,TO_CHAR(R,'YYYY-MM-DD HH24:MI:SS') R,TO_CHAR(T,'YYYY-MM-DD HH24:MI:SS') T  FROM (
    (
        SELECT '1. CC' N, ROUND(SYSDATE, 'CC') R, TRUNC(SYSDATE,'CC') T FROM DUAL -- 세기 , 100년다위
    )
    UNION
    (
        SELECT '2. YEAR' N, ROUND(SYSDATE, 'YEAR') R, TRUNC(SYSDATE,'YEAR') T FROM DUAL --년단위
    )
    UNION
    (
        SELECT '3. Q' N, ROUND(SYSDATE,'Q') R, TRUNC(SYSDATE,'Q') T FROM DUAL --분기
    )
    UNION
    (
        SELECT '4. MONTH' N, ROUND(SYSDATE, 'MONTH') R, TRUNC(SYSDATE,'MONTH') T FROM DUAL --월단위
    )
    UNION
    (
        SELECT '5. W' N, ROUND(SYSDATE, 'W') R, TRUNC(SYSDATE,'W') T FROM DUAL --주단위
    )
    UNION
    (
        SELECT '6. DAY' N, ROUND(SYSDATE, 'DAY') R, TRUNC(SYSDATE,'DAY') T FROM DUAL --일단위
    )
    UNION
    (
        SELECT '7. D' N, ROUND(SYSDATE, 'D') R, TRUNC(SYSDATE,'D') T FROM DUAL
    )
    UNION
    (
        SELECT '8. HH' N, ROUND(SYSDATE, 'HH') R, TRUNC(SYSDATE,'HH') T FROM DUAL
    )
    UNION
    (
        SELECT '9. MI' N, ROUND(SYSDATE, 'MI') R, TRUNC(SYSDATE,'MI') T FROM DUAL
    )
);

[출처] [오라클] 날짜관련 계산하기(1)|작성자

날짜관련 계산하기(1)

단축키

Articles