Skip to content
Advertisement

Filter out records that are not in this date format oracle

How do i filter records that can only be converted to date using to_date('31-May-2019 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

WITH 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 dates_col = ???

from the above sample query i am expecting results below

31-May-2019 00:00:00
15-May-2019 00:00:00
01-Apr-2019 00:00:00
01-Apr-2019

how do i achieve this in oracle.

Reason why i want to do this: i am receiving raw data and loading it to a table before cleaning them to insert clean data to a final table.

Advertisement

Answer

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


User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement