Oracle - Date 날자 관련 함수

 

-- Oracle --
-- 날자 변환 형식 : 2009-01-03  -- > 2009년 01월 03일

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"')AS TODAY FROM DUAL;

-- 현재 날자 시간
SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') FROM DUAL

-- 이틀 전
SELECT TO_CHAR(SYSDATE-2, 'YYYYMMDDHH24MISS') FROM DUAL

-- 3분 전
SELECT TO_CHAR(SYSDATE-(3/(24*60)), 'YYYYMMDDHH24MISS') FROM DUAL

-- 현재 시간에서 20분을 뺀 시간
SELECT TO_CHAR(sysdate - 1/24/60 * 20, 'yyyymmddhh24mi') FROM dual

--------------------------------------------------------------

select /* 오늘날짜 시분초 포함*/
              to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')
    from dual

 

select /* 오늘날짜 00시 00분 00초 */
              to_char(trunc(sysdate),'yyyy/mm/dd hh24:mi:ss')
    from dual

 

select /* 오늘날짜 00시 00분 00초 위와 동일*/
              to_char(trunc(sysdate,'dd'),'yyyy/mm/dd hh24:mi:ss')
    from dual

select /* 이번달 1일 00시 00분 00초 */
              to_char(trunc(sysdate,'mon'),'yyyy/mm/dd hh24:mi:ss')
    from dual

select /* 올해 1월 1일 00시 00분 00초 */
              to_char(trunc(sysdate,'year'),'yyyy/mm/dd hh24:mi:ss')
    from dual

 

select /* 올해 1월 1일 00시 00분 00초 */
              to_char(to_date('2002','yyyy'),'yyyy/mm/dd hh24:mi:ss')
    from dual

 

select /* 2월 1일 00시 00분 00초 */
              to_char(to_date('200202','yyyymm'),'yyyy/mm/dd hh24:mi:ss')
    from dual

select /* 2월 2일 00시 00분 00초 */
              to_char(to_date('20020202','yyyymmdd'),'yyyy/mm/dd hh24:mi:ss')
    from dual

 

select /* 2월 2일 00시 00분 01초 */
              to_char(to_date('20020202','yyyymmdd')+1/68400,'yyyy/mm/dd hh24:mi:ss')
    from dual

 

select /* 2월 2일 00시 00분 00초 -> 한달뒤*/
              to_char(add_months(to_date('20020202','yyyymmdd'),1),'yyyy/mm/dd hh24:mi:ss')

 from dual

 

from en-core

laalaal~

 

 

날짜 빼기

 

밑에 날짜 빼기가 있던데 요건 약간 다르게..

(1) 현재 날자에서 하루를 빼고 싶다고 하면
            select sysdate() - 1 from dual

(2) 1시간을 빼고 싶으면
            select sysdate() - 1/24 from dual

(3) 1분을 빼고 싶으면
            select sysdate() - 1/24/60

(q) 1초를 빼고 싶은면 어떻게 할까요? ^^

 

======================================================================================
- 날짜형 함수

    SYSDATE : 현재 시스템의 날짜 및 시간을 구함

 

LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함

-- 현재의 월의 마지막날자를 반환
SELECT LAST_DAY(SYSDATE) "REMAIN DAYS" FROM DUAL;

-- 현재 월의 남은 일수를 반환
SELECT (LAST_DAY(SYSDATE) - SYSDATE) "REMAIN DAYS" FROM DUAL;

MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함

-- MONTHS_BETWEEN(DATA1, DATA2)
-- 날자와 날자 사이의 기간을 월 로 나타냄. data1이 data2보다 큰 값

SELECT MONTHS_BETWEEN(LAST_DAY('20091231'), SYSDATE) "남은달" FROM DUAL;
남은달
---------------
10.81507616...

    ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함

    ROUND : 날짜에 대한 반올림

    TRUNC : 날짜에 대한 버림

    SYSDATE : SYSDATE → 10-MAY-99

    LAST_DAY(날짜값) : LAST_DAY('17-FEB-98') → 28-FEB-98

   MONTHS_BETWEEN(날짜값1, 날짜값2) : MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323

   ADD_MONTHS(날짜값, 숫자값) : ADD_MONTHS('22-JUL-95',21) → 22-APR-97

      ROUND(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.

                              ROUND(SYSDATE,'MONTH') → 01-MAY-99

      TRUNC(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.

                              TRUNC(SYSDATE,'YEAR') → 01-JAN-99

  - 날짜에 대한 산술연산

    날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산

    날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산

    날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산

- 변환형 함수

오늘은 MS-SQl을 쓰다가 필요한 쿼리를 찾던 중 TO_CHAR로 된 쿼리를 찾았는데 써보니 MS-SQL용 쿼리가 아니라 ORACLE용 쿼리여서 제대로 쓸수가 없었습니다.
그래서 찾던 중 ORACLE의 TO_CAHR 함수를 MS-SQL의 CONVERT 함수로 대체할 수 있다는 것을 찾아서 이렇게 정리해 봅니다.

혹시나 저처럼 필요하신 분은 아래를 참조하시면 되겠습니다!!

출처 : ORACLE의 TO_CHAR 함수를 MSSQL의 CONVERT 함수로

※ ORACLE에서 날짜를 처리할때는
TO_CHAR(SYSDATE,'YYYY-MM-DD') -> 2003-01-23
TO_CHAR(SYSDATE,'YYYY/MM/DD') -> 2003/01/23
TO_CHAR(SYSDATE,'YYYYMMDD') -> 20030123

※ 반대로 처리할때는 TO_DATE함수를 사용하면 되죠~

※ ORACLE에서 숫자를 처리할때는
TO_CHAR(2500000,'L9,999,999') -> w2,500,000
TO_CHAR(2500000,'9,999,999.99') -> 2,500,000.00

※ 반대로 처리할때는 TO_NUMBER함수를 사용하면 되죠~
※ 이 외에도 활용할 수있는 용도가 무지 많습니다.

※ MSSQL에서 날짜를 처리할때는
CONVERT(VARCHAR(10),GETDATE(),120) -> 2003-01-23
CONVERT(VARCHAR(10),GETDATE(),111) -> 2003/01/23
CONVERT(VARCHAR(8),GETDATE(),112) -> 20030123

※ MSSQL에서 숫자를 처리할때는
CONVERT(varchar(20),   convert(money,2500000),1) -> 2,500,000.00


출처 : DBMS별 날짜 포맷변환

1. DBMS 별 시간, 날짜 조회 쿼리

Oracle	select sysdate from dual;  날짜+시분초 까지 조회가능 select current_timestamp from dual;  날짜+밀리초+시간존 까지 조회
MS SQL	select getdate()    날짜 + 밀리초 단위까지 조회가능
DB2 UDB	select current timestamp from sysibm.sysdummy1  날짜+밀리초까지 조회select current date from sysibm.sysdummy1  날짜만 조회 select current time from sysibm.sysdummy1  밀리초 단위시간조회

2. DBMS 별 default date format

Oracle
MS SQL	YYY/MM/DD HH:MI:SS   (한글) MM-DD-YYYY HH:MI:SS   (영어)
DB2 UDB	YYYY-MM-DD-HH:MI:SS.MMMMMM (TIMESTAMP 타입) YYYY-MM-DD (DATE 타입) HH:MI:SS.MMMMMM (TIME 타입)

3. 날짜 포맷 변환표

형식	RDBMS	변환 문법
'YYYY.MM.DD'	Oracle	TO_CHAR(date_exp, 'YYYY.MM.DD')
	MSSQL	CONVERT(VARCHAR, date_exp, 102)
	DB2	REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
	Oracle	TO_CHAR(date_exp, 'HH:MI:SS')
	MSSQL	CONVERT(VARCHAR, date_exp, 108)
	DB2	CHAR(TIME(date_exp) , JIS )
	Oracle	TO_CHAR(date_exp, 'YYYY/MM/DD')
	MSSQL	CONVERT(VARCHAR, date_exp, 111)
	DB2	REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')
	Oracle	TO_CHAR(date_exp, 'YYYYMMDD')
	MSSQL	CONVERT(VARCHAR, date_exp, 112)
	DB2	CHAR(DATE(date_exp))
	Oracle	TO_CHAR(date_exp, 'HH24:MI:SS')
	MSSQL	CONVERT(VARCHAR(8), date_exp, 114)
	DB2	CHAR(TIME(date_exp) )
	Oracle	TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')
	MSSQL	CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)
	DB2	REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))
	Oracle	TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')
	MSSQL	CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)
	DB2	REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))

Oracle/PLSQL: To_Date Function

---

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.