[Solved] Filter out records that are not in this date format oracle

by


You can use an inline function to check if the date is valid. Like this:

WITH 
FUNCTION is_valid_date (date_str_i VARCHAR2, format_i VARCHAR2) RETURN VARCHAR2
/* check if date is valid */
AS
  l_dummy_dt DATE;
BEGIN
  SELECT TO_DATE(date_str_i,format_i) INTO l_dummy_dt FROM DUAL;
  RETURN 'Y';
EXCEPTION WHEN OTHERS THEN
  RETURN 'N';
END; 
dates  AS (
SELECT '0201-05-31 00:00:00' dates_col FROM dual UNION ALL
SELECT '31-May-2019 00:00:00' FROM dual UNION ALL
SELECT 'TEXT' FROM dual UNION ALL
SELECT '15-May-2019 00:00:00' FROM dual UNION ALL
SELECT '01-Apr-2019 00:00:00' FROM dual UNION ALL
SELECT '01-Apr-2019' FROM dual 
) SELECT *
  FROM dates
 WHERE is_valid_date(dates_col,'DD-MON-YYYY HH24:MI:SS') = 'Y';

solved Filter out records that are not in this date format oracle