Put Default Values for date filters-CodePudding

Below is the code for my filters in a chart in APEX. Wanted to know if I could put in default values for the date filters, otherwise they are null and wont display anything until a date is inputed. There is a "Date From" and "Date to" value. Ideally I'd like for date to to be the current date and the date from to be from a year ago.

(MODEL = :P9_Model or :P9_Model is null) 
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND SCRAP_DATE > :P9_DATE_FROM 
AND SCRAP_DATE < :P9_DATE_TO

CodePudding user response：

(MODEL = :P9_Model or :P9_Model is null) 
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND ((:P9_DATE_FROM IS NULL AND SCRAP_DATE > sysdate - INTERVAL '1' YEAR)
      OR (SCRAP_DATE > :P9_DATE_FROM)
    )
AND ((:P9_DATE_TO IS NULL AND SCRAP_DATE < SYSDATE)
      OR (SCRAP_DATE < :P9_DATE_TO)
    )

or use NVL

(MODEL = :P9_Model or :P9_Model is null) 
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND SCRAP_DATE > NVL(:P9_DATE_FROM,SYSDATE - INTERVAL '1' YEAR)
AND SCRAP_DATE < NVL(:P9_DATE_TO,SYSDATE)

As a side note, it is advised to always pass the date format when converting strings to dates. :P9_DATE_FROM is a string (everything in apex is a string) so it's better to write TO_DATE(:P9_DATE_FROM,'DD-MON-YYYY'). Replace the 'DD-MON-YYYY' with the format of your choice. Same for :P9_DATE_TO

CodePudding user response：

you can try now()

(MODEL = :P9_Model or :P9_Model is null) 
AND (Paint_shop = :P9_Select_Shop or :P9_Select_Shop is null)
AND (Color = :P9_COLOR or :P9_COLOR is null)
AND (Department_Code = :P9_DEPARTMENT_CODE or :P9_DEPARTMENT_CODE is null)
AND ( :P9_DATE_FROM = now() or  SCRAP_DATE > :P9_DATE_FROM  )
AND ( :P9_DATE_TO = now() or  SCRAP_DATE < :P9_DATE_TO )