PL/SQL Convert string to date when some values are not in a date format?

Question

I have a string field where 99% of the entries are in a format that can be converted to a date, i.e. To_DATE(' 20100501', 'yyyymmdd') would work great. However, about 1% of the entries are in as something like '00000000' or '00000500'. So when I try to apply the TO_DATE function to all of the values, i.e. SELECT TO_DATE(datefield, 'yyyymmdd') the result is an error (not a valid month/year) because the year or month is not valid for some of the entries. I am at a loss for what to do here. Perhaps I can make the TO_DATE function part of a sub query after a date validation has been applied? Maybe convert those non date formats to something like 19000101 to avoid the error???

Answer 1

The best solution is to first convert the broken values to valid dates (or to null or store them in a new field) and then convert the column to the DATE type.

Using VARCHAR columns for DATE values is poor design for many reasons. You have stumbled on one of them.

Answer 2

SELECT  CASE
            WHEN to_number(a_date) > 19000101
            THEN to_date(a_date, 'YYYYMMDD')
            ELSE DATE'1900-01-01'
        END d_date
FROM
(
        SELECT '20130101' a_date FROM dual UNION ALL
        SELECT '20120101'        FROM dual UNION ALL
        SELECT '20110101'        FROM dual UNION ALL
        SELECT '20100101'        FROM dual UNION ALL
        SELECT '00000000'        FROM dual UNION ALL
        SELECT '00000500'        FROM dual
)
;
/*
2013-01-01 00:00:00
2012-01-01 00:00:00
2011-01-01 00:00:00
2010-01-01 00:00:00
1900-01-01 00:00:00
1900-01-01 00:00:00
*/