时间戳

首页 > 解决方案 > 如何在 MySQL 中使用 Case When 和 Str_to Date 将 Varchar 日期值转换为日期数据类型

mysql - 如何在 MySQL 中使用 Case When 和 Str_to Date 将 Varchar 日期值转换为日期数据类型

问题描述

我在当前将该列保存为 Varchar 数据类型的表中有一个日期列表。我要做的是清理这个表，第一步是将此列转换为日期数据类型，并将我必须的值都转换为一致。

以下是我的创建和插入语句，这是日期列的示例：

创建表：

CREATE TABLE “BU_TABLE_DATES" 
   (           "END_DATE" VARCHAR2(255 BYTE)
   );

插入语句：

INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('01/01/2018');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-18');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-19');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-19');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13-Jan-20');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13/01/2018');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13/01/2019');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('13/01/2020');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('17/07/2017');
INSERT INTO "BU_TABLE_DATES" (END_DATE) VALUES ('17/07/2019');

我希望最终输出如下所示（所有 Date 数据类型）

End_Date
01/01/2018
13/01/2018
13/01/2019
13/01/2019
13/01/2020
13/01/2018
13/01/2019
13/01/2020
13/01/2020
17/07/2017
17/07/2019

目前，我已经设法使用 case 语句和 str_to_date 编写了以下代码，这有助于转换月份只有 2 个字符的值，以及即使编写了代码，我也可以在末尾显示年份的格式那样。

我的查询

SELECT
    End_Date,
    CASE End_Date
        WHEN substring(End_Date,2,1) = '-' THEN STR_TO_DATE (End_Date,'%d-%m-%Y')
        WHEN substring(End_Date,2,1) = '/' THEN STR_TO_DATE (End_Date,'%d/%m/%Y')
    ELSE STR_TO_DATE (End_Date,'%d/%m/%Y') END as End_Date_New
FROM
    BU_TABLE_DATES
;

输出

End_Date    End_Date_New
01/01/2018  2018-01-01
13-Jan-18   (null)
13-Jan-19   (null)
13-Jan-19   (null)
13-Jan-20   (null)
13/01/2018  2018-01-13
13/01/2019  2019-01-13
13/01/2020  2020-01-13
13/01/2020  2020-01-13
17/07/2017  2017-07-17
17/07/2019  2019-07-17

最后在代码中我正在创建一个新列，但我只想更改现有列的数据类型，但目前找到了一种创建新列的方法，然后认为最好删除旧列，但理想情况下希望这适用于 1 列。

希望得到一些关于如何最好地解决这个问题的建议。

提前致谢。

标签： mysqlsubstringstr-to-date

解决方案

您可以使用以下将字符串转换为日期。存储日期时，您确实应该使用DATE-datatype。使用VARCHAR日期只会给你带来麻烦。

SELECT
  End_Date,
  CASE substring(End_Date,3,1)
    WHEN '-' THEN STR_TO_DATE(End_Date,'%d-%b-%y')
    WHEN '/' THEN STR_TO_DATE(End_Date,'%d/%m/%Y')
  END as End_Date_New
FROM 
  BU_TABLE_DATES

请参阅手册CASE中的语句和STR_TO_DATE-function 参数（与DATE_FORMAT's 相同）。

推荐阅读