- 날짜+밀리초까지 조회 가능
select current timestamp from sysibm.sysdummy1;
- 날짜만 조회
select current date from sysibm.sysdummy1;
- 밀리초 단위의 시간만 조회
select current time from sysibm.sysdummy1;
- 요일
select dayname(current DATE,'KR') from sysibm.sysdummy1;
--
-- DB2 UDB UDF(User-Defined Function) Samples for Migration
--
-- 2001/09/05, 2002/07/21(Add GETDATE, GETUTCDATE)
--
-- Name of UDF: SYSDATE ()
-- GETDATE ()
-- GETUTCDATE ()
--
-- Used UDF: None
--
-- Description: SYSDATE, GETDATE
-- Return current timestamp.
--
-- GETUTCDATE
-- Returns current UTC time (Universal Time Coordinate or Greenwich Mean Time).
--
-- Author: TOKUNAGA, Takashi
--
--------------------------------------------------------------------------
CREATE FUNCTION SYSDATE ()
RETURNS Timestamp
LANGUAGE SQL
SPECIFIC SYSDATEOracle
NOT DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN
CURRENT TIMESTAMP;
---------------------------------------------------
DB20000I The SQL command completed successfully.
--------------------------------------------------------------------------
CREATE FUNCTION GETDATE ()
RETURNS Timestamp
LANGUAGE SQL
SPECIFIC GETDATESQLServer
NOT DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN
CURRENT TIMESTAMP;
---------------------------------------------------
DB20000I The SQL command completed successfully.
--------------------------------------------------------------------------
CREATE FUNCTION GETUTCDATE ()
RETURNS Timestamp
LANGUAGE SQL
SPECIFIC GETUTCDATEMSServer
NOT DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN
CURRENT TIMESTAMP - CURRENT TIMEZONE;
---------------------------------------------------
DB20000I The SQL command completed successfully.
--------------------------------------------------------------------------
select sysdate() from sysibm.sysdummy1;
---------------------------------------------------
1
--------------------------
2001-09-05-15.24.58.686001
1 record(s) selected.
자동증가 필드로 테이블생성
create TABLE SENDDATA
(
SeqNo INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1, NO CACHE ) ,
InDate CHAR(8) CCSID 833 NOT NULL DEFAULT '' ,
Member DECIMAL(11,0) NOT NULL DEFAULT 0 ,
ID CHAR(16) CCSID 833 NOT NULL DEFAULT '' ,
message CHAR(120) CCSID 833 NOT NULL DEFAULT '' ,
Rdate CHAR(8) CCSID 833 NOT NULL DEFAULT '' ,
sendtime CHAR(6) CCSID 833 NOT NULL DEFAULT '' ,
Result CHAR(1) CCSID 833 NOT NULL DEFAULT '' ,
Type CHAR(1) CCSID 833 NOT NULL DEFAULT '' ,
Code DECIMAL(11,0) NOT NULL DEFAULT 0 ,
Etc CHAR(14) CCSID 833 NOT NULL DEFAULT '' ,
CONSTRAINT SENDDATA_PK
PRIMARY KEY (SeqNo)
);
LABEL ON TABLE SENDDATA IS 'SEND DATA';
LABEL ON COLUMN SENDDATA
(
SeqNo TEXT IS 'SEQ NO' ,
InDate TEXT IS 'IN DATE' ,
Member TEXT IS 'MEMBER' ,
ID TEXT IS 'ID' ,
message TEXT IS 'MESSAGE' ,
Rdate TEXT IS 'R DATE' ,
sendtime TEXT IS 'SEND TIME' ,
Result TEXT IS 'RESULT' ,
Type TEXT IS 'TYPE' ,
Code TEXT IS 'CODE' ,
Etc TEXT IS 'ETC'
);
CREATE INDEX SENDDATA_01
ON SENDDATA (Result, Type);
CREATE INDEX SENDDATA_02
ON SENDDATA (sendtime);
select TABNAME,COLNAME,TYPESCHEMA,LENGTH,NULLS,DEFAULT,REMARKS FROM SYSCAT.COLUMNS
Where TABNAME='ECR'