Selecting dates in PL/SQL

เงื่อนไข : เลือกข้อมูลระหว่างวันที่ 01/01/ปี-2 (คือย้อนหลัง 2 ปี)
ถึง 30/06/ปี-2


INSERT INTO tum1_his
select absent_date from tabsentdate0_his
where absent_date
between to_date('01/01/'||to_char(add_months(sysdate,-24),' YYYY'),'dd/mm/yyyy')
and to_date('30/06/'||to_char(add_months(sysdate,-24),' YYYY'),'dd/mm/yyyy');


/* Full */

l_str_procdate:='20100801';

--Tabsentdate0
-- Mth02 do mth01-06 (year)-1
if(substr(l_str_procdate,5,4) = '0201') THEN

INSERT INTO tum1_his
select absent_date from tabsentdate0_his
where absent_date
between to_date('01/01/'||to_char(add_months(sysdate,-24),' YYYY'),'dd/mm/yyyy')
and to_date('30/06/'||to_char(add_months(sysdate,-24),' YYYY'),'dd/mm/yyyy');

-- Mth08 do mth07-12 (year)-1

elsif (substr(l_str_procdate,5,4)='0801') THEN

INSERT INTO tum1_his
select absent_date from tabsentdate0_his
where absent_date
between to_date('01/07/'||to_char(add_months(sysdate,-24),'
YYYY'),'dd/mm/yyyy')
and to_date('31/12/'||to_char(add_months(sysdate,-24),'
YYYY'),'dd/mm/yyyy');
end if;

*******************************************
How to LOG into table:

TXT_SQL:= 'DELETE FROM '||SCH_NAME||'.tum1_his '||
' WHERE TO_CHAR(action_date,''YYYYMM'') '||
' <= TO_CHAR(TO_NUMBER('||l_str_year||')-1)||''06'' ';

TXT_LOG:= 'INSERT INTO '||SCH_NAME||'.LOG_TUNETIME '||
' (M_TABLE,H_TABLE,ACTION,TOTAL_RECORD,PROC_NAME,CRT_DATE) '||
' SELECT '||
' ''tum1_his'',''NULL'',''DELETE'', '||
' (SELECT count(*) FROM TUM1_HIS '||
' WHERE to_char(action_date,''YYYYMM'') '||
' <= TO_CHAR(TO_NUMBER('||l_str_year||')-1)||''06'') '||
' ,''PROC_TIME_PROCESS'','''||l_sysdate||''' '||
' FROM DUAL ';


execute immediate TXT_LOG;
execute immediate TXT_SQL;