Valdating Varchar column contains valid date and also validate leap year or not in Oracle SQL

Question

CREATE TABLE DATE_TEST(DD VARCHAR2(40));

Insert into DATE_TEST VALUES('30-02-24');
Insert into DATE_TEST VALUES('12-01-24');
INSERT INTO DATE_TEST VALUES('15');

I Want to list out the invalid rows from the above table in SQL.

so the expected OUTPUT

DD
-
30-02-24
15

Answer 1

From Oracle 12.2, you can use TO_DATE with DEFAULT NULL ON CONVERSION ERROR and then check if the converted value is NULL:

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

Which, for the sample data, outputs:

DD
30-02-24
15

In earlier versions, create a user-defined function:

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;
/

Then you can use:

SELECT *
FROM   date_test
WHERE  is_valid_date(dd) = 0

Which outputs the same.

fiddle