System Functions

SELECT DAYS(doj) FROM emp;

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

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

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

SELECT YEAR(ORDER_DATE) as TEMP_HY;

SELECT EXTRACT (YEAR FROM ORDER_DATE) as TEMP_HY;

SELECT CURRENT DATE FROM DUAL;

SELECT CURRENT_DATE FROM DUAL;

SELECT CURRENT TIMESTAMP - 7 DAYS;

SELECT CURRENT_TIMESTAMP - 7 DAYS;

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

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

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

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

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;