Validate Date

Here is a script along with PL/SQL function to validate date data that may be passed from various sources to an Oracle database. The specified date format is "dd-MON-yyyy" for accurate loading. In the sample data below there are four invalid date data that has to be filtered out and typically error codes have to be designated for fast resolution. The script validates date data using regular expression functions and PL/SQL function. The data anomalies are identified for easy resolution. Invalid date such as "29-FEB-2014" has to be validated using a PL/SQL function validate_date(date_string) since the data complies with the defined format.

The sames validation can be done using PL/SQL function and Translate function.

WITH q_t AS
 (SELECT 1  idx, '15-JAN-2011'  date_str FROM DUAL  UNION
  SELECT 2  idx, 'FEB-15-2012'  date_str FROM DUAL  UNION
  SELECT 3  idx, '25-JAN-2013'  date_str FROM DUAL  UNION
  SELECT 4  idx, '25-FEB-yyyy'  date_str FROM DUAL  UNION
  SELECT 5  idx, '29-FEB-2014'  date_str FROM DUAL  UNION
  SELECT 6  idx, '29-JON-2015'  date_str FROM DUAL),
q_mon AS
 (SELECT
    q_t.idx,
    q_t.date_str,
    DECODE(NVL(REGEXP_SUBSTR(q_t.date_str,'^\d{2}'),0), 0,
      SUBSTR(q_t.date_str,1,2)) invalid_day,
    DECODE(qt_mon.valid_month,NULL, SUBSTR(q_t.date_str,4,3)) invalid_month,
    DECODE(REGEXP_SUBSTR(q_t.date_str,'\d{4}$'), NULL, SUBSTR(q_t.date_str,8,4)) invalid_year
  FROM q_t,
    (SELECT  TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),level-1),'MON') valid_month
     FROM DUAL CONNECT BY level <=12) qt_mon
  WHERE SUBSTR(q_t.date_str,4,3) = qt_mon.valid_month (+))
SELECT * FROM q_mon
WHERE (NOT REGEXP_LIKE(date_str,'^\d{2}') OR
       REGEXP_SUBSTR(date_str,'\d{4}$') IS NULL OR
       validate_date(date_str) = 0)
ORDER BY idx;

Date Data Validation using Regular Expression

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 25, 2012

72868