Oracle Apex Date Picker odd behavior

Programming
, ,
1

I am using Oracle Apex 19.2 to build a dynamic report based on a Classic report and a PL/SQL function body returning an SQL statement. I have two date pickers, P5_CLOSED_SINCE and P5_IGNORED_SINCE, which are set by default to sysdate - 21 upon page refresh via a dynamic action. The PL/SQL code is as follows:

declare
  clsd_snc date:= :P5_CLOSED_SINCE;
  ignrd_snc date := :P5_IGNORED_SINCE;
begin
  return 'select ' || clsd_snc || ',' || ignrd_snc || ' from dual';
end;

When the form loads, the date pickers display an incorrect value of 12/15/2019. When I manually set the date pickers to a proper date, the output of the report is .000495049504950495049504950495049504950495. I have tried using to_date and to_char in the PL/SQL, as well as changing the date format of the pickers, but nothing is working. I would hugely appreciate any help with this issue.

2

The issue is with the PL/SQL code where the dates are not being converted to a string format properly. Update the PL/SQL code as follows:

declare
  clsd_snc varchar2(100);
  ignrd_snc varchar2(100);
begin
  clsd_snc := to_char(:P5_CLOSED_SINCE, 'MM/DD/YYYY');
  ignrd_snc := to_char(:P5_IGNORED_SINCE, 'MM/DD/YYYY');
  return 'select ' || clsd_snc || ',' || ignrd_snc || ' from dual';
end;

This code will convert the dates to a string format before concatenating them with the SQL statement.