Validate date value format : Utility Function « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Utility Function

Validate date value format

  
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION validate_date_format
  2     (p_input_date in VARCHAR2) RETURN BOOLEAN IS
  3     myResult DATE;
  4  BEGIN
  5     myResult := TO_DATE(p_input_date, 'MM/DD/YYYY');
  6     IF LENGTH(SUBSTR(p_input_date,
  7        INSTR(p_input_date, '/', 1, 2) + 1)) = 4 THEN
  8        RETURN TRUE;
  9     ELSE
 10        RETURN FALSE;
 11     END IF;
 12  EXCEPTION
 13     WHEN OTHERS THEN
 14        RETURN FALSE;
 15  END validate_date_format;
 16  /

Function created.

SQL>
SQL> show error
No errors.
SQL>
SQL> BEGIN
  2     IF validate_date_format('&&valid_date') THEN
  3        DBMS_OUTPUT.PUT_LINE('Date: ' || '&&valid_date' ||
  4           CHR(9) || ' is a VALID Date.');
  5     ELSE
  6        DBMS_OUTPUT.PUT_LINE('Date: ' || '&&valid_date' ||
  7           CHR(9) || ' is an INVALID Date.');
  8     END IF;
  9  END;
 10  /
old   2:    IF validate_date_format('&&valid_date') THEN
new   2:    IF validate_date_format('') THEN
old   3:       DBMS_OUTPUT.PUT_LINE('Date: ' || '&&valid_date' ||
new   3:       DBMS_OUTPUT.PUT_LINE('Date: ' || '' ||
old   6:       DBMS_OUTPUT.PUT_LINE('Date: ' || '&&valid_date' ||
new   6:       DBMS_OUTPUT.PUT_LINE('Date: ' || '' ||
Date:    is an INVALID Date.

PL/SQL procedure successfully completed.

SQL>

   
  

Related examples in the same category