select * from pur_bsh where
inv_date = '01-feb-15'
it return correct rowsdeclare
d_f date;
d_t date;
begin
d_f := :BASIC_BLOCK.D_from;
d_t := :BASIC_BLOCK.D_to;
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = d_f');
GO_BLOCK('pur_bsh');
EXECUTE_QUERY;
end ;
d_f := to_date('01-feb-15','DD-mon-YY');
message(d_f);
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= d_f ');
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(''01/01/2011'',''MM/DD/YYYY'')');
set_block_property('pur_bsh',default_where,'inv_date >=to_date('''|| :BASIC_BLOCK.D_from ||''',''dd-mm-rrrr'')');
and it works SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date = to_date(:pur_bsh.field_name,''MM/DD/YYYY'')');
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(''' || :BASIC_BLOCK.D_from || ''',''mm/dd/yyyy'') and to_date(''' || :BASIC_BLOCK.D_to || ''',''mm/dd/yyyy'')');
the above code return same error : unable to perform querySET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date between to_date(:BASIC_BLOCK.D_from,''mm/dd/yyyy'') and to_date(:BASIC_BLOCK.D_to,''mm/dd/yyyy'')');
SET_BLOCK_PROPERTY('pur_bsh',DEFAULT_WHERE,'inv_date >= to_date(''' || to_char(d_f, 'mmddyyyy') || ''',''mmddyyyy'') and inv_date <= to_date(''' || to_char(d_t, 'mmddyyyy') || ''',''mmddyyyy'')');
You mention 2 different date formats.
You say that 01-FEB-15 works and that in the form the date format is MM/DD/YYYY. If you specify 02/01/15 with a date format of MM/DD/YYYY, what you are actually searching for is 02/01/0015. I thought there was a change to make that a format mask error, but I just tried it in a 11 database and I did not get an error.
Anyway, explicit conversion is always the way to go. You can never count on the client not changing the default format mask.