<div id="readability-page-1" class="page"><div class="page">
 
 
<div id="dw__toc">
<h3>목차</h3>

</div>



출처 : <a href="http://dbdbdic.tistory.com/112" target="_blank" title="http://dbdbdic.tistory.com/112" rel="ugc nofollow noopener">http://dbdbdic.tistory.com/112</a>

<pre>CREATE TABLE TEMP_DATE(
 ID INT,
 INDATE DATETIME
 ) 
&nbsp;
INSERT INTO TEMP_DATE (ID, INDATE )
 SELECT '1', SYSDATE FROM OPENQUERY(ORACLE_LINK,'SELECT SYSDATE FROM DUAL')
&nbsp;
SELECT * FROM TEMP_DATE
&nbsp;
-- 날짜 08 22 2006 의 형식을 2006-08-22 00:00:00 으로 변경하기 
&nbsp;
SELECT @t_PLAN_STARTDATE = CAST(@Plan_StartDate AS DATETIME )
SELECT @t_PLAN_ENDDATE = CAST(@Plan_EndDate AS DATETIME )</pre>

<h2 id="dbms에_따른_날짜포맷_변환">DBMS에 따른 날짜포맷 변환</h2>

Oracle - MS SQL - DB2 UDB 의 서로 다른 날짜 형식을 맞추기위한 SQL문


<h3 id="dbms_별_시간_날짜_조회_쿼리">DBMS 별 시간, 날짜 조회 쿼리</h3>
<div>


1. Oracle



날짜+시분초 까지 조회가능

<pre>SELECT sysdate FROM dual;
-- 날짜+밀리초+시간존 까지 조회
SELECT CURRENT_TIMESTAMP FROM dual;</pre>


2. MS SQL

<pre>-- 날짜 + 밀리초 단위까지 조회가능
SELECT getdate();</pre>


3. DB2 UDB

<pre>-- 날짜+밀리초까지 조회 가능
SELECT CURRENT TIMESTAMP FROM sysibm.sysdummy1;
-- 날짜만 조회
SELECT CURRENT DATE FROM sysibm.sysdummy1;
-- 밀리초 단위의 시간만 조회
SELECT CURRENT TIME FROM sysibm.sysdummy1;</pre>

</div>

<h3 id="dbms_별_default_date_format">DBMS 별 default date format</h3>
<div>


1. Oracle

<div><table>
	<tbody><tr>
		<td> 한글 : YYYY/MM/DD </td><td> 영어 : DD-MON-YYYY </td>
	</tr>
</tbody></table></div>


2. MS SQL

<div><table>
	<tbody><tr>
		<td> 한글 : YYYY/MM/DD HH:MI:SS </td><td> 영어 : MM-DD-YYYY HH:MI:SS </td>
	</tr>
</tbody></table></div>


3. DB2 UDB

<div><table>
	<tbody><tr>
		<td> TIMESTAMP 타입 : YYYY-MM-DD-HH:MI:SS.MMMMMM </td>
	</tr>
	<tr>
		<td> DATE 타입 : YYYY-MM-DD </td>
	</tr>
	<tr>
		<td> TIME 타입 : HH:MI:SS.MMMMMM </td>
	</tr>
</tbody></table></div>

</div>

<h3 id="날짜_포맷_변환">날짜 포맷 변환</h3>
<div>


[ 형식 : 'YYYY.MM.DD' ]

<pre>-- Oracle
TO_CHAR(date_exp, 'YYYY.MM.DD')
-- MSSQL
CONVERT(VARCHAR, date_exp, 102)
-- DB2
REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')</pre>


[ 형식 : 'HH:MI:SS' ]

<pre>-- Oracle
TO_CHAR(date_exp, 'HH:MI:SS')
-- MSSQL
CONVERT(VARCHAR, date_exp, 108)
-- DB2
CHAR(TIME(date_exp) , JIS )</pre>


[ 형식 : 'YYYY/MM/DD' ]

<pre>-- Oracle
TO_CHAR(date_exp, 'YYYY/MM/DD')
-- MSSQL
CONVERT(VARCHAR, date_exp, 111)
-- DB2
REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')</pre>


[ 형식 : 'YYYYMMDD' ]

<pre>-- Oracle
TO_CHAR(date_exp, 'YYYYMMDD') |
-- MSSQL
CONVERT(VARCHAR, date_exp, 112) |
-- DB2
CHAR(DATE(date_exp)) |</pre>


[ 형식 : 'HH24:MI:SS' ]

<pre>-- Oracle
TO_CHAR(date_exp, 'HH24:MI:SS') |
-- MSSQL
CONVERT(VARCHAR(8), date_exp, 114) |
-- DB2
CHAR(TIME(date_exp)) |</pre>


[ 형식 : 'YYYY.MM.DD HH24:MI' ]

<pre>-- 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))</pre>


[ 형식 : 'YYYY/MM/DD HH24:MI:SS' ]

<pre>-- 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))</pre>

</div>


 
 </div></div>

GINS WIKI 긴스 위키

문기은

출처 : http://dbdbdic.tistory.com/112
CREATE TABLE TEMP_DATE(
  ID     INT,
  INDATE DATETIME
 ) 
 
INSERT INTO TEMP_DATE (ID, INDATE )
  SELECT '1', SYSDATE FROM OPENQUERY(ORACLE_LINK,'SELECT SYSDATE FROM DUAL')
 
SELECT * FROM TEMP_DATE
 
-- 날짜   08 22 2006 의 형식을  2006-08-22 00:00:00 으로 변경하기 
 
SELECT @t_PLAN_STARTDATE = CAST(@Plan_StartDate    AS DATETIME  )
SELECT @t_PLAN_ENDDATE = CAST(@Plan_EndDate    AS DATETIME  )
DBMS에 따른 날짜포맷 변환
DBMS 별 시간, 날짜 조회 쿼리
SELECT sysdate FROM dual;
-- 날짜+밀리초+시간존 까지 조회
SELECT CURRENT_TIMESTAMP FROM dual;
-- 날짜 + 밀리초 단위까지 조회가능
SELECT getdate();
-- 날짜+밀리초까지 조회 가능
SELECT CURRENT TIMESTAMP FROM sysibm.sysdummy1;
-- 날짜만 조회
SELECT CURRENT DATE FROM sysibm.sysdummy1;
-- 밀리초 단위의 시간만 조회
SELECT CURRENT TIME FROM sysibm.sysdummy1;
DBMS 별 default date format
	
 한글 : YYYY/MM/DD  영어 : DD-MON-YYYY 
	
2. MS SQL
 한글 : YYYY/MM/DD HH:MI:SS  영어 : MM-DD-YYYY HH:MI:SS 
	
3. DB2 UDB
 TIMESTAMP 타입 : YYYY-MM-DD-HH:MI:SS.MMMMMM 
	
 DATE 타입 : YYYY-MM-DD 
	
 TIME 타입 : HH:MI:SS.MMMMMM 
	
날짜 포맷 변환
-- 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))

DBMS에 따른 날짜포맷 변환

댓글