TYPE	         CODE	            VALUE
MISSING_RECORD	AUG_2010	AUGUST 2010

CREATE TABLE "REF_TABLE"
  (
  "TYPE"  VARCHAR2(20 BYTE) NOT NULL ENABLE,
  "CODE"  VARCHAR2(20 BYTE) NOT NULL ENABLE,
  "VALUE" VARCHAR2(100 BYTE)
  );
 
Insert into Ref_table (TYPE,CODE,VALUE) values ('MISSING_RECORD','AUG_2010','AUGUST 2010');

01-AUG-2010 31-AUG-2010

SELECT TRUNC(VALUE , 'Month'),LAST_DAY(TRUNC(VALUE , 'Month')) MONTH FROM 
ref_table where code =  'MISSING_RECORD';

SELECT TRUNC(VALUE , 'Month'),LAST_DAY(TRUNC(VALUE , 'Month')) MONTH FROM 
ref_table where code =  'MISSING_RECORD';

ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 15 Column: 47

SELECT  TO_CHAR(TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm'),'fmMonth DD, YYYY'),
        TO_CHAR(LAST_DAY(TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm')),'fmMonth DD, YYYY')
  FROM  ref_table
  WHERE code =  'MISSING_RECORD';

select 1 from Main_table
where col_1 between '01-AUG-2010' and '31-AUG-2010'

using the below values

SELECT  TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm'),
        LAST_DAY(TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm'))  FROM 
ref_table where type =  'MISSING_RECORD';

select 1 from Main_table
where col_1 not between values from second query




SELECT  TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm'),
        LAST_DAY(TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm'))  FROM 
ref_table where type =  'MISSING_RECORD';

CREATE TABLE "MAIN_TABLE"
  (
  "INT_AMT"           NUMBER(12,2),
  "CHARGE_END_DATE"   DATE,
  "NO_OF_PARTIES"     NUMBER(3,0),
  "CHARGE_START_DATE" DATE
  );
 
Insert into MAIN_TABLE (INT_AMT,CHARGE_END_DATE,NO_OF_PARTIES,CHARGE_START_DATE) values (0,to_date('30/09/2005','DD/MM/YYYY'),2,to_date('31/10/2005','DD/MM/YYYY'));
Insert into MAIN_TABLE(INT_AMT,CHARGE_END_DATE,NO_OF_PARTIES,CHARGE_START_DATE) values (0,to_date('01/12/2005','DD/MM/YYYY'),2,to_date('29/12/2005','DD/MM/YYYY'));
Insert into MAIN_TABLE (INT_AMT,CHARGE_END_DATE,NO_OF_PARTIES,CHARGE_START_DATE) values (0,to_date('01/11/2005','DD/MM/YYYY'),2,to_date('30/11/2005','DD/MM/YYYY'));

select * from summary sum,
(SELECT  TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm') START_DATE,
        LAST_DAY(TRUNC(TO_DATE(VALUE,'Month YYYY'),'mm')) END_DATE  FROM 
Ref_table ref WHERE TYPE =  'MISSING_RECORD') ps
and sum.charge_start_date not between ps.start_date and ps.end_date