checking Date data type values in dirty column

Database
N/A

checking Date data type values in dirty column

I have following column, name dataOfBirth, of type string

14-2-1982
3-9-1978
Ho. no.185 street #
14-6-1976
25-10-1984
21-5-1979
9-2-1979

I want to find valid date from above column and remove invalid string,
the format is same of all date values.

How to solve this problem.
1 REPLY
N/A

Re: checking Date data type values in dirty column

If the data is always that format you might use a join to sys_calendar.calendar:

SELECT x, calendar_date
FROM dropme LEFT JOIN sys_calendar.calendar
ON TRIM(x) = TRIM(EXTRACT(DAY FROM calendar_date))
|| '-' || TRIM(EXTRACT(MONTH FROM calendar_date))
|| '-' || TRIM(EXTRACT(YEAR FROM calendar_date))

Dieter