MySQL 字符串日期格式转换

在MySQL中，经常需要将字符串类型的日期转换成日期或时间类型，或者在不同的日期格式之间进行转换。MySQL提供了几种方法来实现这一点，包括使用STR_TO_DATE()和DATE_FORMAT()函数。

1. 使用STR_TO_DATE()函数

STR_TO_DATE()函数可以将字符串转换成日期或时间格式。它接受两个主要参数：要转换的字符串和格式字符串。

语法:

STR_TO_DATE(str, format)

• str是要转换的字符串。
• format是该字符串的日期或时间格式。

示例:
假设你有一个日期字符串'2023-04-01'，并且它是以'YYYY-MM-DD'格式存储的，但你希望将它转换成日期类型以进行日期计算。

SELECT STR_TO_DATE('2023-04-01', '%Y-%m-%d') AS converted_date;

这将返回日期类型的结果，格式为'2023-04-01'。

2. 使用DATE_FORMAT()函数

DATE_FORMAT()函数用于将日期或时间值格式化为指定的格式。虽然这主要用于格式化日期时间值，但你也可以用它来“转换”日期到字符串的特定格式。

语法:

DATE_FORMAT(date, format)

• date是日期或时间值。
• format是你希望返回的日期时间格式。

示例:
如果你有一个日期类型的值，并且希望将其格式化为'YYYYMMDD'格式的字符串。

SELECT DATE_FORMAT(NOW(), '%Y%m%d') AS formatted_date;

这里NOW()函数返回当前的日期和时间，然后DATE_FORMAT()将其格式化为'YYYYMMDD'格式的字符串。

3. 转换和格式化的结合使用

有时，你可能需要先将字符串转换成日期类型，然后再将其格式化为另一种格式的字符串。这可以通过结合使用STR_TO_DATE()和DATE_FORMAT()来实现。

示例:
假设你有一个日期字符串'01-Apr-2023'，并且你希望将其转换为'YYYYMMDD'格式的字符串。

SELECT DATE_FORMAT(STR_TO_DATE('01-Apr-2023', '%d-%b-%Y'), '%Y%m%d') AS formatted_string;

这里，STR_TO_DATE()首先将字符串转换为日期类型，然后使用DATE_FORMAT()将其格式化为'YYYYMMDD'。

总结

通过STR_TO_DATE()和DATE_FORMAT()函数，MySQL允许你在字符串和日期类型之间转换，并在不同的日期格式之间自由转换。这在进行数据清洗、处理和报表生成时非常有用。

起因

需要将Oracle中的数据导到MySQL中

#阻碍

在Oracle导出数据时，发现导出的SQL脚本中的日期转换是to_date('28-11-2023 14:15:17', 'dd-mm-yyyy hh24:mi:ss')的形式，但MySQL并不认识这个格式转换函数，此时需要将Oracle中这个日期转换函数替换成MySQL中的日期格式转换函数才能在MySQL中执行导出的脚本，那么在MySQL中日期如何转换呢？

#分析

在to_date('28-11-2023 14:15:17', 'dd-mm-yyyy hh24:mi:ss')中，是要将给定的字符串28-11-2023 14:15:17按照dd-mm-yyyy hh24:mi:ss的格式转换成日期，那么我们也需要在MySQL找到一个函数能够将指定的字符串按照一定的格式转换成日期。

#解决

在MySQL中可以使用str_to_date(str,format)这个函数来实现将字符串按照时间格式转换成日期。那么对于Oracle中的to_date('28-11-2023 14:15:17', 'dd-mm-yyyy hh24:mi:ss')可以修改成str_to_date('28-11-2023 14:15:17', '%d-%m-%Y %H:%i:%s')，替换后就可以在MySQL执行进行日期转换了。

验证，结果如下：

#总结

1. Oracle中字符串转日期：to_date('28-11-2023 14:15:17', 'dd-mm-yyyy hh24:mi:ss')
2. MySQL中字符串转日期：str_to_date('28-11-2023 14:15:17', '%d-%m-%Y %H:%i:%s')
3. MySQL常见时间格式化：（与字符串中的对应使用）
   1. %Y： 年份，四位数字
   2. %m：月份，两位数字
   3. %d： 日期，两位数字
   4. %H：小时（24小时制），两位数字
   5. %i： 分钟，两位数字
   6. %s： 秒，两位数字
4. 扩展：MySQL中如果想将日期转换成字符串可以使用date_format(date,format)