[오라클] 날짜 함수- 현재시각으로부터 ~ 후 계산하기 / 날짜와 날짜 빼기/ 년월일 숫자로 추출/ 만나이 구하기/ 특정 달의 첫 날, 마지막 날 구하기
0
oracle
목록 보기
11/40
날짜1 + 숫자 = 날짜2 ==> 날짜1에서 숫자(일수) 만큼 더한 값이 날짜2가 된다.
날짜1 - 숫자 = 날짜2 ==> 날짜1에서 숫자(일수) 만큼 뺀 값이 날짜2가 된다.
여기서 중요한 것은 숫자의 단위는 일수이다.
현재시각으로부터 ~ 후 계산하기
SELECT
sysdate, -- 지금 날짜를 알려줌, 시간까지 현재 시각
sysdate - 1, -- 날짜에서 하루를 빼서 어제 날짜가 된다
sysdate + 1,
to_char(sysdate - 1, 'yyyy-mm-dd hh24:mi:ss') AS "어제시각",
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS "현재시각",
to_char(sysdate + 1, 'yyyy-mm-dd hh24:mi:ss') AS "내일시각"
FROM dual;
--- 단위환산 ---
/*
1 kg = 1000 g
1 g = 1/1000 kg
1 일 = 24 시간
1 시간 = 60 분
1 분 = 1/60 시간
1 분 = 60 초
1 초 = 1/60 분
*/
QUIZ
현재시각으로부터 1일 2시간 3분 4초 뒤를 나타내세요
SELECT
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS "현재시각",
to_char(sysdate + 1 + (2/24) + 3/(24*60) + 4/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') AS "1일 2시간 3분 4초 뒤"
FROM dual;
-- 2024-02-19 21:48:51 2024-02-20 23:51:55ADD_MONTH('날짜', 숫자)
- 숫자가 양수일 때 : 날짜에서 숫자 개월수만큼 더해준 날짜를 나타냄
- 숫자가 음수일 때 : 날짜에서 숫자 개월수 만큼 뺀 날짜를 나타냄
(여기서 숫자의 단위는 개월 수이다.) - 2월, 4월 리턴에 주의하기 (2월-29일까지, 4월-30일까지)
SELECT
add_months(sysdate, -2) AS "2개월전",
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS "현재시각",
to_char(add_months(sysdate,2), 'yyyy-mm-dd hh24:mi:ss') AS "2개월후"
FROM dual;QUIZ
현재시각으로부터 1개월 2일 3시간 4분 5초 뒤를 나타내세요.
SELECT
TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') AS "현재시각",
TO_CHAR( ADD_MONTHS(sysdate + 2 + (3/24) + 4/(24*60) +5/(24*60*60), 2), 'yyyy-mm-dd hh24:mi:ss' ) AS "1개월 2일 3시간 4분 5초 뒤"
FROM dual;
날짜에서 날짜 빼는 MONTHS_BETWEEN(날짜1, 날짜2)
- 날짜1 - 날짜2 = 개월 수
- 즉, 두 날짜의 개월 차이를 구할 때 사용한다.
SELECT
-- 날짜에서 +3개월한 값 - 현재날짜 = 3
MONTHS_BETWEEN(ADD_MONTHS(SYSDATE,3), SYSDATE) --3(숫자 타입)
FROM dual;
년월일 숫자 형태로 출력하는 EXTRACT(보고싶은 정보 FROM 날짜)
- 날짜 정보 추출 함수
- to_char을 이용해서 할 수 있겠지만 extract로도 가능
SELECT
sysdate,
EXTRACT(year FROM sysdate), TO_CHAR(sysdate, 'yyyy'),
EXTRACT(month FROM sysdate), TO_CHAR(sysdate, 'mm'),
EXTRACT(day FROM sysdate), TO_CHAR(sysdate, 'dd')
FROM dual;
QUIZ
1. 주민등록번호 뒤의 자리 *로 마스킹 처리하기
SELECT
employee_id AS "사원번호",
first_name || ' ' || last_name AS "사원명",
jubun AS "기존 주민번호",
-- 넣을 값 총문자길이 빈공간채움문자
RPAD(SUBSTR(jubun, 1, 7), LENGTH(jubun), '*') AS "마스킹처리 주민번호"
FROM
employees;
2. 태어난 년도 칼럼 구하기
SELECT
employee_id AS "사원번호",
first_name || ' ' || last_name AS "사원명",
jubun AS "기존 주민번호",
RPAD(SUBSTR(jubun, 1, 7), LENGTH(jubun), '*') AS "마스킹처리 주민번호",
CASE SUBSTR(jubun, 7, 1) -- 이 상태에서는, case문처럼 값이 딱딱 일치해야해서 in 사용 불가.
WHEN '1' THEN '19'
WHEN '2' THEN '19'
ELSE '20'
END || SUBSTR(jubun, 1, 2) AS "태어난년도 1",
CASE
WHEN SUBSTR(jubun, 7, 1) IN('1','2') THEN '19'
ELSE '20'
END || SUBSTR(jubun, 1, 2) AS "태어난년도 2",
TO_NUMBER(SUBSTR(jubun, 1, 2)) +
CASE
WHEN SUBSTR(jubun, 7, 1) IN('1','2') THEN 1900
ELSE 2000
END AS "태어난년도 3",
DECODE( SUBSTR(jubun, 7, 1), '1', '19'
, '2', '19'
, '20') || SUBSTR(jubun, 1, 2) AS 태어난년도3,
to_number( substr(jubun, 1, 2) ) + case when substr(jubun, 7, 1) in('1','2') then 1900 else 2000 end AS 태어난년도4,
to_number( substr(jubun, 1, 2) ) + decode( substr(jubun, 7, 1), '1', 1900, '2', 1900, 2000 ) AS 태어난년도5
FROM
employees;
-- 풀이 :
-- 주민번호에서 1,2 면 90년대생. 3,4면 00년대생 -> 주민번호 위치 구함
-- case 문으로 작성 (리턴값과 남은 년도를 붙임)
-- 숫자로 변환해서 더하는 방법도 있음
3. 만나이 구하기
SELECT
employee_id AS "사원번호",
first_name || ' ' || last_name AS "사원명",
jubun AS "기존 주민번호",
TO_NUMBER(SUBSTR(jubun, 1, 2)) + CASE WHEN SUBSTR(jubun, 7, 1) IN('1','2') THEN 1900 ELSE 2000 END AS "태어난 년도",
TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) AS 현재년도,
TO_DATE(TO_CHAR(sysdate, 'yyyy') || SUBSTR(jubun, 3, 4), 'yyyymmdd') AS "올해생일", -- char타입을 다시 date타입으로
TO_DATE(TO_CHAR(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') AS "현재일",
CASE
WHEN TO_DATE(TO_CHAR(sysdate, 'yyyy') || SUBSTR(jubun, 3, 4), 'yyyymmdd') - TO_DATE(TO_CHAR(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') > 0 -- g
THEN TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) - TO_NUMBER( CASE WHEN SUBSTR(jubun, 7, 1) IN('1','2') THEN '19'ELSE '20' END || SUBSTR(jubun, 1, 2)) -1
ELSE TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) - TO_NUMBER( CASE WHEN SUBSTR(jubun, 7, 1) IN('1','2') THEN '19'ELSE '20' END || SUBSTR(jubun, 1, 2))
END AS "만나이"
FROM
employees;
-- 올해 생일이 현재일과 같거나,과거라면(생일 지났으면) 현재년도 - 태어난년도
-- 올해 생일이 현재일 보다 미래 라면 (생일 안 지났으면) 현재년도 - 태어난년도 - 1
SELECT
employee_id AS "사원번호",
first_name || ' ' || last_name AS "사원명",
jubun AS "기존 주민번호",
TO_NUMBER(SUBSTR(jubun, 1, 2)) + CASE WHEN SUBSTR(jubun, 7, 1) IN('1','2') THEN 1900 ELSE 2000 END AS "태어난 년도",
TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) AS 현재년도,
TO_DATE(TO_CHAR(sysdate, 'yyyy') || SUBSTR(jubun, 3, 4), 'yyyymmdd') AS "올해생일", -- char타입을 다시 date타입으로
TO_DATE(TO_CHAR(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') AS "현재일",
CASE
WHEN TO_DATE(TO_CHAR(sysdate, 'yyyy') || SUBSTR(jubun, 3, 4), 'yyyymmdd') - TO_DATE(TO_CHAR(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') > 0 -- 현재일 - 생일해서 양수가 나오면 생일 지남
THEN TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) - (to_number( substr(jubun, 1, 2) ) + decode( substr(jubun, 7, 1), '1', 1900, '2', 1900, 2000 )) - 1 --현재년도 - 태어난년도 - 1
ELSE TO_NUMBER(TO_CHAR(sysdate, 'yyyy')) - (to_number( substr(jubun, 1, 2) ) + decode( substr(jubun, 7, 1), '1', 1900, '2', 1900, 2000 ))
END AS "만나이"
FROM
employees;
last_day(특정날짜)
- 특정날짜가 포함된 달력에서 맨 마지막 날짜를 알려주는 것이다.
SELECT sysdate, last_day(sysdate) -- 결과값 24/02/19 , 24/02/29
FROM dual;
next_day(특정날짜, '찾을 요일')
- 특정날짜로부터 다음번에 돌아오는 가장 빠른 '일'~ '토' 의 날짜를 알려주는 것이다.
- 특정날짜에서 -8일을 빼주면 이전 요일에 해당하는 날짜를 구할 수 있다.
SELECT
sysdate,
next_day(sysdate,'금'),
next_day(sysdate,'월')
FROM dual;
-- 20(화) 23(금) 26(월)
-- 결과 : 24/02/20 24/02/23 24/02/26
참고하면 좋음
현재 시각/날짜 알려주기, WHERE문에 날짜로 찾기 / 아스키 코드
https://velog.io/@nime0110/%EC%98%A4%EB%9D%BC%ED%81%B4-%ED%98%84%EC%9E%AC-%EC%8B%9C%EA%B0%81%EB%82%A0%EC%A7%9C-%EC%95%8C%EB%A0%A4%EC%A3%BC%EA%B8%B0date
개발하는 심심이