I am having a Procedure , which is accepting fromDate and toDate input parameters I need to set default values as First date of Last month and last date of previous month respectively. I am trying to use below code, but still proper default dates are not being set. Please let me know if below code is valid or i can do something to correct it:
create or replace PROCEDURE "TEST"
(
fromdate_in IN varchar2,
todate_in IN varchar2,
type_in IN number DEFAULT 01
)
is
V_date varchar2(3000);
begin
select to_date(fromdate_in) into V_date from dual; -- Correct date entered
Exception WHEN Others THEN
select to_char(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'DD/MM/RRRR') into V_date from dual; -- if fromdate_in --is null then set V_date to first date of Previous month
-- calculations using V_date
end TEST;
Please note , I have shown only how I am setting first date of Previous month to From Date for simplicity.
You could do it much easier like this:
create or replace PROCEDURE "TEST"
(
fromdate_in IN DATE,
todate_in IN DATE,
type_in IN number DEFAULT 1
)
is
V_date DATE;
begin
V_date := NVL(fromdate_in, TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'));
-- calculations using V_date
end TEST;
If you are forced to use VARCHAR2
for fromdate_in
then convert the value to DATE
:
V_date := NVL(TO_DATE(fromdate_in, 'DD/MM/YYYY'), TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'));