mysql - 如何从以不同格式存储日期的 varchar 列中获取有效日期

Question

我在 MySql 的表中有一列，其中包含任何格式的字符串日期(没有固定模式)，它可以是 m-d-y 或 M-D-Y 或 m/d/y 或 YY/MM/DD 或 MM/DD/YY 或 mm/dd/yy 等。我的问题是我应该如何检测它然后更改某些特定格式，即 mm/dd/yy。

Answer 1

如果您能猜出您将日期存储在 varchar 字段中的不同格式的数量，那么处理这个问题会更容易；

一种方法是将所有这些不同的格式放在下面给出的查询中:

SELECT 
COALESCE(
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  STR_TO_DATE(your_date_column,'%M-%D-%Y'),
  STR_TO_DATE(your_date_column,'%m/%d/%Y'),
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  ....
  .
  .

)
FROM your_table;

演示:

SET @your_date_field := '8/8/2016';

SELECT 
COALESCE(
  STR_TO_DATE(@your_date_field,'%m-%d-%Y'),
  STR_TO_DATE(@your_date_field,'%M-%D-%Y'),
  STR_TO_DATE(@your_date_field,'%m/%d/%Y'),
  STR_TO_DATE(@your_date_field,'%M/%D/%Y')
);

输出: 2016-08-08 (yyyy-mm-dd)

注意:

但日期应该存储在 date 数据类型中。违反这一点将导致这种麻烦的情况。

因此，最好将这些日期字符串移至日期数据类型列。

---

更多:为了将这些日期字符串移动到date 数据类型列，您可以按照以下步骤操作:

ALTER TABLE your_table ADD COLUMN date_new date; 

UPDATE 
your_table
SET date_new = COALESCE(
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  STR_TO_DATE(your_date_column,'%M-%D-%Y'),
  STR_TO_DATE(your_date_column,'%m/%d/%Y'),
  STR_TO_DATE(your_date_column,'%m-%d-%Y'),
  ....
  .
  .

);


ALTER TABLE your_table DROP COLUMN `date`;

ALTER TABLE datestable CHANGE COLUMN `date_new` `date` date;