오라클 날짜 관련 컨버젼

 작성자 : 김민석 ( lemonfish at gmail dot com )

---

0	Feb 22 2006 4:26PM	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)
1	02/22/06	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)
2	06.02.22	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)
3	22/02/06	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)
4	22.02.06	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)
5	22-02-06	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)
6	22 Feb 06	CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)
7	Feb 22, 06	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)
8	16:26:08	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)
9	Feb 22 2006 4:26:08:020PM	CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)
10	02-22-06	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)
11	06/02/22	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)
12	060222	CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)
13	22 Feb 2006 16:26:08:020	CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13)
14	16:26:08:037	CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14)
20	2006-02-22 16:26:08	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20)
21	2006-02-22 16:26:08.037	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)
22	02/22/06 4:26:08 PM	CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)
23	2006-02-22	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)
24	16:26:08	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)
25	2006-02-22 16:26:08.037	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)
100	Feb 22 2006 4:26PM	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100)
101	02/22/2006	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101)
102	2006.02.22	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)
103	22/02/2006	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
104	22.02.2006	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104)
105	22-02-2006	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105)
106	22 Feb 2006	CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106)
107	Feb 22, 2006	CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)
108	16:26:08	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108)
109	Feb 22 2006 4:26:08:067PM	CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)
110	02-22-2006	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110)
111	2006/02/22	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111)
112	20060222	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)
113	22 Feb 2006 16:26:08:067	CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113)
114	16:26:08:067	CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)
120	2006-02-22 16:26:08	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120)
121	2006-02-22 16:26:08.080	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)
126	2006-02-22T16:26:08.080	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
127	2006-02-22T16:26:08.080	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127)
130	24 ???? 1427 4:26:08:080PM	CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)
131	24/01/1427 4:26:08:080PM	CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131)

 

 

 

--------------------------------------------------------------------------------
날짜 포맷 변환
--------------------------------------------------------------------------------
[ 형식 : 'YYYY.MM.DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102)
3. DB2 : REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
 
[ 형식 : 'HH:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 108)
3. DB2 : CHAR(TIME(date_exp) , JIS )

 

[ 형식 : 'YYYY/MM/DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')

[ 형식 : 'YYYYMMDD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYYMMDD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 112)
3. DB2 : CHAR(DATE(date_exp))

[ 형식 : 'HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : CHAR(TIME(date_exp))

[ 형식 : 'YYYY.MM.DD HH24:MI' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))

[ 형식 : 'YYYY/MM/DD HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))

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

 

/* 테이블의 칼럼정보 보기( Oracle 의 desc 테이블명 ) */
exec sp_columns sales

/* Substring 처리 */
select payterms from sales
select substring(payterms,1,3) from sales

/* 문자열연결 연산자 '+' */
select substring(payterms,1,4)+ ' 테스트' from sales

/*현재날짜 */
select getdate()
결과 : 2002-05-09 10:51:45.870

/* 1년후날짜 */
select getdate() + 365
결과 : 2003-05-09 10:50:43.020

/* 날짜데이타 변경 CONVERT(변경될문자열의 데이타타입및길이, 날짜데이타, 출력형식) */
select convert(varchar(30), getdate(), 2)
결과 : 02.05.09

select convert(varchar(30), getdate(), 102)
결과 : 2002.05.09

/* YYYYMMDD 형식으로 가져오기( Varchar(8)로 설정 ) */
select convert(varchar(8), getdate(), 112)
결과 : 20020509

/* 해당 [월] 만 가져오기 */
select datepart(mm,getdate())
select month(getdate())
결과 : 5

/* 현재일자에 20개월추가 (월 추가) */
select dateadd(mm,20,getdate())
결과 : 2004-01-09 10:56:57.580

/* 현재일자에 100일 후의 날짜 */
select dateadd(dd,100,getdate())

/* Oracle 에서의 NVL함수에 대응하는 MS-SQL함수 */
NVL(price,0) ===> ISNULL(price,0)

/* 판매수량이 30권 이상인책에 대하여 각 서적별 판매수량총계 조회 */
select title_id as '서적코드' , sum(qty) as '판매수량' from sales
group by title_id
having sum(qty) >= 30

/* COMPUTE & COMPUTE BY 사용법 */
select type, title_id, price from titles
order by type
compute avg(price) by type
해당 select칼럼이 별로 Average값을 구해준다. ( Order by 반드시 필요 )

select type, title_id, price from titles
order by type
compute avg(price) by type
마지막행에 Select된 전체칼럼중 Average(price)칼럼에 대해 평균값을 구해준다.

/* Rollup & Cube */
select type, avg(price) from titles group by type --각 타입별 평균
select avg(price) from titles -- 전체평균