Oracle Date Format with input Parametres - PLSQL

Question

I'm getting an error as follows: ORA-01858 - a non-numeric character was found where a numeric character was expected. Although it isn't really accurate, I believe it's related to the date formatting in the following lines.

ELSIF par_report_eff_date_start IS NOT NULL AND par_report_eff_date_start = to_date(par_report_eff_date_start, 'fxdd-mm-yyyy')

and

ELSIF par_report_eff_date_end IS NOT NULL AND par_report_eff_date_end = to_date( par_report_eff_date_end ,'fxDD-MM-YYYY')

I'm trying to get the parameters to render date in the format of 'dd/mm/yyyy' as it is passed in, but i'm not sure how to get around this.

I have looked, but have limited web access at work, so I can't use the regular sites.

       procedure collect_mon_comm_bal_data_part (
  par_report_eff_date_start DATE DEFAULT NULL, --dd/mm/yyyy;
  par_report_eff_date_end  DATE DEFAULT NULL) --dd/mm/yyyy;
  is 

v_report_eff_date_start DATE;
v_report_eff_date_end DATE;

BEGIN

  IF par_report_eff_date_start IS NULL 
    THEN 
    -- Oracle job runs at the beginning of each month
      select trunc(trunc(sysdate,'Mon')-1,'Mon')
      into v_report_eff_date_start
      from dual;   -- Start of month Var
    ELSIF par_report_eff_date_start IS NOT NULL AND par_report_eff_date_start = to_char(par_report_eff_date_start, 'fxdd/mm/yyyy')
        THEN
          v_report_eff_date_start := par_report_eff_date_start;
          DBMS_OUTPUT.PUT_LINE(par_report_eff_date_start || 'Is The Start Date');
    ELSE  
          DBMS_OUTPUT.PUT_LINE(par_report_eff_date_start || 'Is is the wrong format, needs tp be in dd/mm/yyyy'); 
          GOTO the_end;
  END IF;


  IF par_report_eff_date_end IS NULL 
    THEN 
    -- Oracle job runs at the beginning of each month
      select  trunc(sysdate,'MM')-1
      into v_report_eff_date_end
      from dual;   -- Start of month Var
    ELSIF par_report_eff_date_end IS NOT NULL AND par_report_eff_date_end = to_char(par_report_eff_date_end, 'fxdd/mm/yyyy')
        THEN
          v_report_eff_date_end := par_report_eff_date_end;
          DBMS_OUTPUT.PUT_LINE(par_report_eff_date_end || 'Is The Start Date');
     ELSE
          DBMS_OUTPUT.PUT_LINE(par_report_eff_date_end || 'Is is the wrong format, needs tp be in dd/mm/yyyy');
            GOTO the_end;
  END IF;
END;

Answer 1

You don't want to convert a date to a date. You want to convert it to a character string. So try:

to_char(par_report_eff_date_start, 'fxdd-mm-yyyy')

I'm not sure what the rest of the logic is supposed to be doing. But, you use to_date() to convert a value to a date and to_char() to convert a value to a string.

Answer 2

I notice that you have parameter par_report_eff_date_start, and a variable v_report_eff_date_start. You probably want to compare those in the following line:

ELSIF par_report_eff_date_start IS NOT NULL AND par_report_eff_date_start = to_char(par_report_eff_date_start, 'fxdd/mm/yyyy')

But now you are comparing the date parameter with a text value derived from the parameter value. I don't think that's what you want. A date is just a date, and you only have to think about format masks when converting it to another data type (like VARCHAR2). So why not use:

ELSIF par_report_eff_date_start IS NOT NULL AND trunc(par_report_eff_date_start) = trunc(sysdate, 'Mon')

I've added trunc() to the parameter value, and because you want the first day of the month (I guess!!), trunc(sysdate, 'Mon') will do just fine.

Answer 3

Thank You Gordon, I think I've found a way around it.

       procedure collect_mon_comm_bal_data_part (   
    par_report_eff_date_start VARCHAR2 DEFAULT NULL, --dd/mm/yyyy;  
 par_report_eff_date_end  VARCHAR2 DEFAULT NULL) --dd/mm/yyyy;   is 

        v_report_eff_date_start DATE; v_report_eff_date_end DATE;

        BEGIN

          IF par_report_eff_date_start IS NULL 
            THEN 
            -- Oracle job runs at the beginning of each month
              select trunc(trunc(sysdate,'Mon')-1,'Mon')
              into v_report_eff_date_start
              from dual;   -- Start of month Var
            ELSIF par_report_eff_date_start IS NOT NULL AND to_date(par_report_eff_date_start, 'dd/mm/yyyy') = to_date(par_report_eff_date_start,'fxdd/mm/yyyy') 
                THEN
                  v_report_eff_date_start := to_date(par_report_eff_date_start, 'dd/mm/yyyy');
                  DBMS_OUTPUT.PUT_LINE(par_report_eff_date_start || 'Is The Start Date');
            ELSE  
                  DBMS_OUTPUT.PUT_LINE(par_report_eff_date_start || 'Is is the wrong format, needs tp be in dd/mm/yyyy'); 
                  GOTO the_end;   END IF;


          IF par_report_eff_date_end IS NULL 
            THEN 
            -- Oracle job runs at the beginning of each month
              select  trunc(sysdate,'MM')-1
              into v_report_eff_date_end
              from dual;   -- Start of month Var
            ELSIF par_report_eff_date_end IS NOT NULL AND to_date(par_report_eff_date_end, 'dd/mm/yyyy') = to_date(par_report_eff_date_end,'fxdd/mm/yyyy') 
                THEN
                  v_report_eff_date_end := to_date(par_report_eff_date_end, 'dd/mm/yyyy');
                  DBMS_OUTPUT.PUT_LINE(par_report_eff_date_end || 'Is The Start Date');
             ELSE
                  DBMS_OUTPUT.PUT_LINE(par_report_eff_date_end || 'Is is the wrong format, needs tp be in dd/mm/yyyy');
                    GOTO the_end;   END IF;

So I want it to default to a monthly run, for the previous month if the parameters are null. And I want it to run for selected dates, should they be put in manually at execution, this way I can run it for a year period to build up a proper sample of data, that will be updated as it changes going forward.

I have tried to pass in bogus dates, and null dates and they hit the exception or run for a month of data as expected, so this works for me.

Thanks again guys, I really the help!