1

i want to validate date format in my procedure.

create or replace  procedure sp_val_date(pi_id in number,pi_date in varchar2)
is
  lv_date date;
begin
  lv_date:=to_date(pi_date,'DD/MON/YYYY');
  insert into DT_FRMT values(pi_id,lv_date);
  DBMS_OUTPUT.PUT_LINE('Valid date');
exception when others then
   dbms_output.put_line('Not a valid date');

end;

i want enter date in format 'DD/MM/YYYY' but when i entered as 'DD-MM-YYYY' ,my procedure did not throw error.

Toru
  • 905
  • 1
  • 9
  • 28
Mithlesh Singh
  • 11
  • 1
  • 2
  • The proper way of doing it would be `sp_val_date(pi_id in number,pi_date in DATE)` - Then call `TO_DATE()` in the procedure call. – Wernfried Domscheit Dec 15 '19 at 12:33
  • Why do you specify `DD/MON/YYYY` if you want to enter the input value as `DD/MM/YYYY`? – Wernfried Domscheit Dec 15 '19 at 12:34
  • 1
    That's because Oracle is much more flexible onthe format string than you are being. While you want to force a particular character as a separator, Oracle is looking for a vvalid separator but NOT a particular one. While I couldn't find a definitive answer it appears Oracle accepts any separator that passes the regexp [^a-zA-z0-9]. The bigger question would be "is the resulting date correct"? If so does it really matter if I use "01-15-2019" and you use "01/15/2019"? We both get the same result. Just for fun try: select to_date ('01(15)2019','mm-dd-yyyy') from dual; – Belayer Dec 15 '19 at 17:19

2 Answers2

1

Oracle is smart enough to perform datatype conversion, if possible. Have a look at these examples:

I'm setting the default date format mask to dd.mm.yyyy:

SQL> alter session set nls_date_Format = 'dd.mm.yyyy';

Session altered.

No, let's try different formats:

SQL> select to_date('15.12.2019', 'dd.mm.yyyy') val from dual;

VAL
----------
15.12.2019

SQL> select to_date('15.12.2019', 'dd-mm-yyyy') val from dual;

VAL
----------
15.12.2019

SQL> select to_date('15.12.2019', 'dd/mm-yyyy') val from dual;

VAL
----------
15.12.2019

So far, so good. But, if I enter something obviously wrong (there's no 15th month in a year, is there?), it won't work any more:

SQL> select to_date('15.12.2019', 'mm-dd-yyyy') val from dual;
select to_date('15.12.2019', 'mm-dd-yyyy') val from dual
               *
ERROR at line 1:
ORA-01843: not a valid month
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

You can use REGEXP_LIKE for that, e.g.:

CREATE OR REPLACE PROCEDURE sp_val_date(pi_id IN NUMBER, pi_date IN VARCHAR2)
IS
BEGIN
    IF (NOT REGEXP_LIKE(pi_date, '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'))
    THEN
        RAISE_APPLICATION_ERROR(-20000, 'Not a valid date');
     END IF;
     -- May throw an error if date is semantically incorrect, e.g. '99/99/2019' or '29/02/2019'.
     INSERT INTO dt_frmt VALUES (pi_id, TO_DATE(pi_date, 'DD/MM/YYYY'));
END sp_val_date;
/

BEGIN
    -- Okay.
    sp_val_date(1, '12/12/2019');
    -- Error.
    sp_val_date(1, '12-12-2019');
    --Error.
    sp_val_date(1, '29/02/2019');
END;
/

This is a syntactical check only not a semantically! Check also Regex to validate date format dd/mm/yyyy.

Toru
  • 905
  • 1
  • 9
  • 28