Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> DB2 Syntax Migration> System Function
View PDF

System Function

DAYS

DAYS system function

DB2 Syntax

Syntac After Migration

 
SELECT DAYS(doj) FROM emp;
 
SELECT (TRUNC(doj - TO_DATE('0001/01/01', 'YYYY/MM/DD'))+1) FROM emp;

MONTH

Month

DB2 Syntax

Syntac After Migration

 
SELECT (MONTH(ORDER_DATE)-1)/6+1 as TEMP_HY;
 
SELECT (EXTRACT (MONTH FROM ORDER_DATE) -1)/6+1 as TEMP_HY;

YEAR

Year

DB2 Syntax

Syntac After Migration

 
SELECT YEAR(ORDER_DATE) as TEMP_HY;
 
SELECT EXTRACT (YEAR FROM ORDER_DATE) as TEMP_HY;

CURRENT DATE

CURRENT DATE

DB2 Syntax

Syntac After Migration

 
SELECT CURRENT DATE FROM DUAL;
 
SELECT CURRENT_DATE FROM DUAL;

CURRENT TIMESTAMP

CURRENT TIMESTAMP

DB2 Syntax

Syntac After Migration

 
SELECT CURRENT TIMESTAMP - 7 DAYS;
 
SELECT CURRENT_TIMESTAMP - 7 DAYS;

POSSTR

POSSTR

DB2 Syntax

Syntac After Migration

 
SELECT POSSTR('THIS IS TEST','TEST') FROM DUAL;
 
SELECT INSTR('THIS IS TEST','TEST') FROM DUAL;

VALUE

Value

DB2 Syntax

Syntac After Migration

 
Select VALUE('abc','') from dual;
 
Select Coalesce('abc','') from dual;

date

date function is to return a date from a value.

DB2 Syntax

Syntac After Migration

 
SELECT org_code, DATE(order_date)
  FROM view_cc_order
 WHERE order_date = DATE((SELECT start_date FROM year_week_mark 
         WHERE year=TEMP_YEAR and week=TEMP_WEEK));
---
SELECT deptno, deptname, DATE(SELECT max(doj) FROM emp e WHERE e.deptno = d.deptno)
  FROM dept d;
 
SELECT org_code, mig_db2_ext.mig_db2_fn_date(order_date)
  FROM view_cc_order
 WHERE order_date = mig_db2_ext.mig_db2_fn_date((SELECT start_date FROM year_week_mark 
         WHERE year=TEMP_YEAR and week=TEMP_WEEK));
---
SELECT deptno, deptname, mig_db2_ext.mig_db2_fn_date((SELECT max(doj) FROM emp e WHERE e.deptno = d.deptno))
  FROM dept d;
Parent topic: DB2 Syntax Migration