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;

How are you calling the above procedure? My guess is that the calling procedure is passing the date in as a string, not a date, and that the string is not in the correct format. — Boneist
– Boneist, Commented Jan 27, 2015 at 14:42

Gordon Linoff · Accepted Answer · 2015-01-27 13:29:53Z

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.

answered Jan 27, 2015 at 13:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Boneist Over a year ago

In addition to what Gordon has said, if you do to_date() on something that's already in a DATE datatype, Oracle has to do an implicit conversion of the date to a string and then back to a date. It will use whatever nls_date_format mask is set (most likely that of the database, not your client session) to do that. Since the default nls_date_format mask is "dd-MON-yy" there's every chance that you can get the wrong date returned. Try seeing what is returned if you do: select to_char(to_date(to_date('17/05/2080 21:03:32', 'dd/mm/yyyy hh24:mi:ss')), 'dd/mm/yyyy hh24:mi:ss') from dual;

Non Plus Ultra · Accepted Answer · 2015-01-27 14:08:44Z

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.

Ben Coughlan · Accepted Answer · 2015-01-28 15:50:11Z

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!

Collectives™ on Stack Overflow

Oracle Date Format with input Parametres - PLSQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related