更新时间:2022-12-08 GMT+08:00

系统功能

DAYS

系统功能:DAYS。

DB2语法

迁移后语法

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

MONTH

Month

DB2语法

迁移后语法

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语法

迁移后语法

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

当前日期

当前日期

DB2语法

迁移后语法

SELECT CURRENT DATE FROM DUAL;
SELECT CURRENT_DATE FROM DUAL;

当前时间戳

当前时间戳

DB2语法

迁移后语法

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

POSSTR函数

POSSTR函数

DB2语法

迁移后语法

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

VALUE函数

Value函数

DB2语法

迁移后语法

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

date函数

date函数通过值返回日期。

DB2 语法

迁移后语法

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;