Another approach is to try to recover as many dates as possible in different formats using STR_TO_DATE()
, which will return NULL
if the extracted value is invalid, and COALESCE()
to bind the different date formats.
To display only rows with fatal 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 find out what you 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 link as many format lines as you need.
- Use the four-digit
%y
formats before the two digits %y
. Otherwise, you will receive the wrong dates.
If you must have the following sample data:
| ID | DT |
| ---- | ------------- |
| 1 | CANCELLED |
| 2 | 02-Mar-12 |
| 3 | (null) |
| 4 | 5-Aug-13 |
| 5 | |
| 6 | 2013-09-12 |
| 7 | 23/10/2013 |
| 8 | 13-Aug-2012 |
Then the second query produces the 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 | 23/10/2013 | October, 23 2013 00: 00: 00 + 0000 |
| 8 | 13-Aug-2012 | August, 13 2012 00: 00: 00 + 0000 |
Here is the SQLFiddle demo