select data from range of two dates

I want a query for selecting data between two dates (p_startdt,p_enddt) which is given by the user, if there is no input then by default data of last one year will be given as output. I am not able to put case for null or no input

where invc_dt between p_startdt and p_enddt

Answer

Use NVL to handle the case of a NULL value. The following example will take start date as a year ago if p_startdt is null and p_enddt as the current date if p_enddt is null:

WHERE invc_dt 
  BETWEEN NVL(p_startdt,ADD_MONTHS(SYSDATE,-12)) AND 
          NVL(p_enddt,SYSDATE)

Note: I’m assuming the data type of the column invc_dt is DATE.