[SQL] 날짜 연산과 날짜 함수

문자함수 예제 및 문제

--PPT p127
--성의 첫번쨰 글자와 마지막 글자 가같은 사원들의 성과 이름을 보고
SELECT last_name, first_name
FROM employees
WHERE SUBSTR(last_name, 1, 1) = UPPER(SUBSTR(last_name, -1, 1));

SELECT last_name, SUBSTR(last_name,1,1), SUBSTR(last_name,-1,1),UPPER(SUBSTR(last_name, -1,1))
FROM employees; 

--성의 첫두글자+ 밑줄(_)+직무코드 4,5번째글자
SELECT last_name 성, first_name 이름, job_id 직무,
        SUBSTR(last_name,1,2)||'_'||SUBSTR(job_id,4, 2) as 사원코드
FROM employees;  --특정사원에 대해 구하는것이 아니라서 where필요없음

 

반올림, 버림 함수

--반올림, 버림함수
--ROUND 소수점 기준 오른쪽(+) 또는 왼쪽(-)숫자로 반올림또는 반내림
SELECT 45.926,  
    ROUND(45.926) ROUND1, --46
    ROUND(45.926,0) ROUND2, --46
    ROUND(45.926, 2) ROUND3, --45.93
    ROUND(45.926, -2) AS ROUND4, --0
    ROUND(45.926, -1) AS ROUND5, --50
--TRUNC 소수점 기준 오른쪽(+) 또는 왼쪽(-)숫자로 버림
    TRUNC(45.926, 2) TRUNC, --45.92
    TRUNC(45.926, -1) TRUNC2 --40
FROM dual;

 

부동소수점수의 천장값, 바닥값

--천장값, 바닥값
--동등비교는 점값이 내포되어있다
SELECT 45.926,  
--
    CEIL(45.926) CEIL1, --46
    CEIL(-45.926) CEIL2, -- -45
--   
    FLOOR(45.926) FLOOR, -- 45
    FLOOR(45.926)  FLOOR2, -- 45
--
    TRUNC(45.926) TRUNC, -- 45
    TRUNC(45.926) TRUNC2 -- 45
FROM dual;

 

❓FLOW OR TRUNC의 차이점 

 

날짜/시간 데이터

NLS( National Language Support )의 세션 데이터포맷에서 날짜 시간 형식을 확인하고 바꿀 수 있다

SELECT * FROM nls_session_parameters;

한국시간으로는 형식이  RR/MM/DD게 되어있음. 

 

❓NLS( National Language Support )

한국어를 포함하여 다양한 언어로 데이터를 저장, 처리 및 표시하고, 서로 다른 문자 인코딩을 다루며, 현지 관습에 따라 날짜와 숫자를 서식화하고, 언어별 규칙에 따라 문자열을 정렬하고 비교하는 등의 작업을 "NLS"를 통해 수행할 수 있습니다.

SELECT DATE '24-05-07' FROM DUAL; --서기 24년
SELECT DATE '124-05-07' FROM DUAL;  --124년..
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MM-DD'; --날짜형식을 바꾸기
--한국기준의 원래형식은 RR-MM--DD
SELECT * FROM nls_session_parameters; -- NLS확인

--타임스탬프
SELECT TIMESTAMP '24-05-07 11:00:01' FROM DUAL;--년월일시분초를 다 작성해야함
--세션의 날짜 포멧이나 지정한것을 개인적으로 바꿨을 때, 종료 후 재접속하면 원래 지정값으로 돌아옴

 

 

날짜에 숫자 더하기/ ex)오늘 날짜에 하루 더하기 같은..

날짜 +숫자  →  날짜,

날짜 - 숫자 →  날짜

SELECT DATE '24-05-07' + 1
FROM DUAL; 

ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MM-DD HH24:MI:SS'; --형식지정
SELECT DATE '2024-05-07' + 1 + 2/24 + 30/24/60 + 17/24/60/60 FROM DUAL; --오늘로부터 2시간 37분 17초

날짜 -날짜 → 숫자

SELECT DATE '2024-05-07' -  DATE '2024-05-01' AS DATE1, --6일 차이
TIMESTAMP '2024-05-07 12:12:42' -  DATE '2024-05-01' AS TIMESTAP1 --6일 12시간 12분 42초 차이
FROM DUAL;

 

현재 날짜/시간 얻기

오라클 서버시간 각나라마다 다름. 

SELECT SYSDATE, SYSTIMESTAMP FROM DUAL; -- 세계적으로 비즈니스 하려면 타임존 있어야 한다 이말이야..

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; 
--CURRENT타임스페이스는 타임존을을 지역으로, SYSTIMESTAMP는 타임존을 시 분으로 보여주는 차이가있음

 

날짜 함수 

함수	결과
MONTHS_BETWEEN	두 날짜 간의 월 수
ADD_MONTHS	날짜에 월 추가
NEXT_DAY	지정된 날짜의 다음 날
LAST_DAY	월의 마지막날
ROUND	날짜 반올림
TRUNC	날짜 TRUNCATE

 

--NEXT DAY
SELECT SYSDATE, NEXT_DAY(SYSDATE, '화') --오늘날짜에 다음에 오는 화요일 찾기.
       ,NEXT_DAY(DATE '2001-04-13','토') --다음에 오는 토요일 찾기.
FROM DUAL;

날짜에 대한 반올림/버림

--날짜에 대한 반올림/버림
--반올림/버림의 기준을 형식모델로 지정 EX)일을 월로 반올림, 또는 년으로 
SELECT SYSDATE
    ,ROUND(SYSDATE+1/24,'DD') AS DD_날짜를_숫자로봄
    ,ROUND(SYSDATE,'DAY') AS 요일_반올림
    ,ROUND(SYSDATE,'YEAR')AS 월_반올림
    ,ROUND(SYSDATE,'MONTH') AS 일_반올림
    ,ROUND(SYSDATE+60,'YEAR') 
    ,TRUNC(SYSDATE,'YEAR')
    ,ROUND(ADD_MONTHS(SYSDATE,2),'YEAR')  
    ,ROUND(DATE '2024-05-15','MONTH') AS 반올림 --15일까지는 반올림안됨 
    ,TRUNC(ADD_MONTHS(SYSDATE,2),'YEAR')  
    ,SYSDATE+2 AS 내일모레
    ,ROUND(SYSDATE+2,'DAY') --2일후반올림
FROM DUAL;

--2005-2026년에 입사한 사람
SELECT LAST_NAME, FIRST_NAME, HIRE_DATE
    FROM EMPLOYEES
    WHERE HIRE_DATE BETWEEN '2005-02-01' AND '2006-12-30';
 
SELECT LAST_NAME, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE ROUND(HIRE_DATE, 'YYYY') = '2005';  --안되는이유가뭘까 

--8월에 입사한사람