DB2

DBMS별 날짜 포맷변환

시반 2006. 4. 13. 14:52

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            밀리초 단위시간조회  

 

DBMS 별 default date format

Oracle      

YY/MM/DD  (한글)                                                                                      

DD-MON-YYYY  (영어)                                                                                 

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 타입)                                                               

 

날짜 포맷 변환표

 

형식

 

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), '-', '.')

 

 'HH:MI:SS'

Oracle

 

TO_CHAR(date_exp, 'HH:MI:SS')

 

 

MSSQL

 

CONVERT(VARCHAR, date_exp, 108)

 

DB2

 

CHAR(TIME(date_exp) , JIS )

 'YYYY/MM/DD'

 

Oracle

 

TO_CHAR(date_exp, 'YYYY/MM/DD')

 

MSSQL

 

CONVERT(VARCHAR, date_exp, 111)

 

DB2

 

REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')

 'YYYYMMDD'

 

Oracle

 

TO_CHAR(date_exp, 'YYYYMMDD')

 

MSSQL

 

CONVERT(VARCHAR, date_exp, 112)

 

DB2

 

CHAR(DATE(date_exp))

 'HH24:MI:SS'

 

Oracle

 

TO_CHAR(date_exp, 'HH24:MI:SS')

 

MSSQL

 

CONVERT(VARCHAR(8), date_exp, 114)

 

DB2

 

CHAR(TIME(date_exp) )

 'YYYY.MM.DD HH24:MI'

 

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

 'YYYY/MM/DD HH24:MI:SS'

 

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