mysql - 选择所有与日期格式不匹配的行？

Question

我正在尝试迁移/更新一个旧表，该表允许在假定的“日期”字段中使用可为空的 varchar。我想查找所有不符合此格式的行:%e-%b-%y。我怎样才能完成这个查询？

**编辑:我应该提一下，该字段确实包含一些“CANCELLED”、null 或其他字符串值，而不是更常见的 e-b-y 格式。我正在查找这些行，以便将它们更新为我想要的格式 (%e-%b-%y)。

Answer 1

另一种方法是使用 STR_TO_DATE() 尝试恢复尽可能多的不同格式的日期，如果提取的值无效且 将返回 NULL COALESCE() 链接不同的日期格式。

只显示不可恢复日期的行:

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;

查看转换日期后会得到什么:

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;

注意:

• 您可以根据需要链接任意多的格式字符串。
• 在两位数 %y 之前使用四位数格式 %y。否则你会得到不正确的日期。

如果您有以下示例数据:

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

然后第二个查询产生以下输出:

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

这是 SQLFiddle 演示