출처 : http://dbdbdic.tistory.com/112

<code sql>
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  )
</code>

===== DBMS에 따른 날짜포맷 변환 =====

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

==== DBMS 별 시간, 날짜 조회 쿼리 ====

1. Oracle

날짜+시분초 까지 조회가능
<code sql>
select sysdate from dual;
-- 날짜+밀리초+시간존 까지 조회
select current_timestamp from dual;
</code>
 
2. MS SQL

<code sql>
-- 날짜 + 밀리초 단위까지 조회가능
select getdate();
</code>
 
3. DB2 UDB

<code sql>
-- 날짜+밀리초까지 조회 가능
select current timestamp from sysibm.sysdummy1;
-- 날짜만 조회
select current date from sysibm.sysdummy1;
-- 밀리초 단위의 시간만 조회
select current time from sysibm.sysdummy1;
</code>
 

==== DBMS 별 default date format ====

1. Oracle

| 한글 : 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 |
 
==== 날짜 포맷 변환 ====

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

<code sql>
-- Oracle
TO_CHAR(date_exp, 'YYYY.MM.DD')
-- MSSQL
CONVERT(VARCHAR, date_exp, 102)
-- DB2
REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
</code>
 
[ 형식 : 'HH:MI:SS' ]

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

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

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

[ 형식 : 'YYYYMMDD' ]

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

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

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

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

<code sql>
-- 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))
</code>

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

<code sql>
-- 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))
</code>