Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE DATE_TEST(DD VARCHAR2(40));
BEGIN
Insert into DATE_TEST VALUES('30-02-24');
Insert into DATE_TEST VALUES('12-01-24');
INSERT INTO DATE_TEST VALUES('15');
END;
/
1 rows affected

SELECT *
FROM   date_test
WHERE  TO_DATE(dd DEFAULT NULL ON CONVERSION ERROR, 'DD-MM-RR') IS NULL


DD


30-02-24

15


CREATE OR REPLACE FUNCTION is_Valid_Date(
  datestr VARCHAR2,
  format  VARCHAR2 DEFAULT 'FMDD-MM-RR'
) RETURN NUMBER DETERMINISTIC
AS
  x DATE;
BEGIN
  IF datestr IS NULL THEN
    RETURN 0;
  END IF;
  x := TO_DATE( datestr, format );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/
SELECT *
FROM   date_test
WHERE  is_valid_date(dd) = 0


DD


30-02-24

15