Select all rows that dont match a date format?

Question

I am attempting to migrate/update an old table that allowed a nullable varchar in a supposed "date" field. I want to find all rows that don't match this format: %e-%b-%y. How can I accomplish this query?

**EDIT: I should mention that the field does contain a few "CANCELLED", null, or other string values instead of the more common e-b-y format. I am looking for those rows so I can update them to the format I want (%e-%b-%y).

Answer 1

One more approach is to try to recover as much dates as possible with different formats using STR_TO_DATE() which will return NULL if extracted value is invalid and COALESCE() to chain different date formats.

To show only rows with unrecoverable dates:

SELECT *
  FROM table1
 WHERE COALESCE(STR_TO_DATE(NULLIF(dt, ''), '%e-%b-%Y'),
                STR_TO_DATE(NULLIF(dt, ''), '%e-%b-%y'),
                STR_TO_DATE(NULLIF(dt, ''), '%Y-%m-%d'),
                STR_TO_DATE(NULLIF(dt, ''), '%m/%d/%Y'),
                STR_TO_DATE(NULLIF(dt, ''), '%m/%d/%y')) IS NULL;

To see what will you have got after converting dates:

SELECT *, COALESCE(STR_TO_DATE(NULLIF(dt, ''), '%e-%b-%Y'),
                   STR_TO_DATE(NULLIF(dt, ''), '%e-%b-%y'),
                   STR_TO_DATE(NULLIF(dt, ''), '%Y-%m-%d'),
                   STR_TO_DATE(NULLIF(dt, ''), '%m/%d/%Y'),
                   STR_TO_DATE(NULLIF(dt, ''), '%m/%d/%y')) new_date
  FROM table1;

Note:

• You can chain as much format strings as you need.
• Use four digit formats %y before two digits %y. Otherwise you'll get incorrect dates.

If you were to have following sample data:

| ID |          DT |
|----|-------------|
|  1 |   CANCELLED |
|  2 |   02-Mar-12 |
|  3 |      (null) |
|  4 |    5-Aug-13 |
|  5 |             |
|  6 |  2013-09-12 |
|  7 |  10/23/2013 |
|  8 | 13-Aug-2012 |

Then the second query produces following output:

| ID |          DT |                         NEW_DATE |
|----|-------------|----------------------------------|
|  1 |   CANCELLED |                           (null) |
|  2 |   02-Mar-12 |     March, 02 2012 00:00:00+0000 |
|  3 |      (null) |                           (null) |
|  4 |    5-Aug-13 |    August, 05 2013 00:00:00+0000 |
|  5 |             |                           (null) |
|  6 |  2013-09-12 | September, 12 2013 00:00:00+0000 |
|  7 |  10/23/2013 |   October, 23 2013 00:00:00+0000 |
|  8 | 13-Aug-2012 |    August, 13 2012 00:00:00+0000 |

Here is SQLFiddle demo