to_date function pl/sql

Question

undefine dates

declare
  v_dateInput VARCHAR(10);
  v_dates DATE;
begin
  v_dateInput := &&dates;
  v_dates := to_date(v_dateInput,'dd-mm-yyyy');
  DBMS_OUTPUT.put_line(v_dates);
end;

Not sure why whenever I run this code with ,for example , input of 03-03-1990, this error shows up.

Error report:
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 6
01847. 00000 -  "day of month must be between 1 and last day of month"
*Cause:    
*Action:

Answer 1

Ha, good one. That's because && replaces the variable in-place, so your script becomes:

declare
  v_dateInput VARCHAR(10);
  v_dates DATE;
begin
  v_dateInput := 03-03-1990;
  v_dates := to_date(v_dateInput,'dd-mm-yyyy');
  DBMS_OUTPUT.put_line(v_dates);
end;

Note the absence of quotes. v_dateInput is, effectively, '-1990', because oracle calculates the numeric value of 03 - 03 - 1990. Of course, this doesn't work with the given format string.

To fix it, you need

v_dateInput := '&&dates';

Answer 2

When prompted to enter a value for dates make sure you put it in single-quotes, e.g.

Enter value for dates: '03-03-1990'

Share and enjoy.