Oracle Apex：如何在项筛选器中使用日期列

1. Create the Date Filter Item (P1_JOINING_DATE) #

• Navigate to your APEX page editor, add a new item to the region containing your existing P1_EMP_NAME filter.
• Set the item Type to Date Picker (use Date Picker with Range if you want to allow filtering between two dates).
• Name the item P1_JOINING_DATE and set a label like "Joining Date".
• Under Settings, configure the Format Mask to match your employees.joining_date column's format (e.g., DD-MON-YYYY for DATE columns; adjust if using TIMESTAMP).
• Position the item next to P1_EMP_NAME for consistent layout.

2. Update the Interactive Grid's SQL Query #

Modify your existing query to include the date filter logic, ensuring it works alongside the employee name filter:

select emp_id, emp_name, joining_date from employees 
where (:P1_EMP_NAME = 'All' or emp_name = :P1_EMP_NAME)
  and (:P1_JOINING_DATE is null or trunc(joining_date) = trunc(:P1_JOINING_DATE));

• Explanation:
  • :P1_JOINING_DATE is null: Skips the date filter when no date is selected (equivalent to the "All" option for employee names).
  • trunc(joining_date) = trunc(:P1_JOINING_DATE): Matches only the date component (ignores time values if your joining_date column includes them). Omit trunc() if your column stores dates without time.

3. Configure the FIND Button #

• Edit your existing FIND button, go to the Submit Items property.
• Add P1_JOINING_DATE to the list of items to submit (ensure P1_EMP_NAME is still included).
• Keep the button's action as "Submit Page" and confirm it refreshes the interactive grid region.

Optional: Add a Clear Date Filter Button #

If you want an easy way to reset the date filter:

• Add a small button next to P1_JOINING_DATE.
• Set its action to "Execute PL/SQL Code" with the code:

  :P1_JOINING_DATE := null;
  

• Configure it to refresh the interactive grid region after execution.

内容的提问来源于stack exchange，提问作者Velocity