SQL日期格式转换1411报错

MySQL str_to_date问题

我现在有一个csv表，里面有一个字段是SaleDate，导入时选择了varchar，所以现在需要将SaleDate比变成日期格式

主要有两种格式，一个是"%M %d, %Y"，还有一个是"%d-%M-%Y"

第一步：创建了新的字段存储更改后的日期字段
ALTER TABLE house
Add SaleDateConverted Date;

第二部 转换第一种日期
select SaleDate,
STR_TO_DATE(SaleDate,"%M %d, %Y"), SaleDateConverted from house;

update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%M %d, %Y")

运行到这里就会出现1411报错
1411 - Incorrect datetime value: '10-Jun-14' for function str_to_date, Time: 0.001000s

格式转化错误导致的，你表格中同一列SaleDate有两种格式，"%M %d, %Y"，"%d-%M-%Y"
可以使用条件更新
错误代码

select SaleDate,
STR_TO_DATE(SaleDate,"%M %d, %Y"), SaleDateConverted from house;

update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%M %d, %Y")

修改为：

select SaleDate,
STR_TO_DATE(SaleDate,"%M %d, %Y"), SaleDateConverted from house;

update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%M %d, %Y") where SaleDate like '%,%';
update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%d-%M-%Y") where SaleDate like '%-%';

该回答引用ChatGPT

这个错误通常是由于日期格式和实际的日期不匹配导致的。在您的情况下，错误可能是由于 "%d-%M-%Y" 格式无法正确解析 "10-Jun-14" 这样的日期字符串。 这是因为 "%d-%M-%Y" 需要的日期格式应该是 "10-Jun-2014"。

您可以尝试使用以下代码来转换第二种日期格式：

UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%d-%b-%Y');

这里使用了 '%d-%b-%Y' 格式，其中 %b 表示月份的缩写，例如 "Jun"。

此外，请注意，在使用 STR_TO_DATE 函数时，日期和时间必须与 MySQL 的日期时间格式匹配。 如果您的日期或时间格式不匹配，则会出现类似的错误。 您可以使用 DATE_FORMAT 函数来查看 MySQL 当前日期时间格式的示例。

由于数据格式不严谨，你应该做一个自定义函数，来适配可能出现的情况，比如 英文月/日/年，数字月/日/年，日/月/年，中文的x年x月x日

并且，在无法适配的情况下，也应该返回一个数据 null 或默认日期，来避免函数异常

该回答引用GPTᴼᴾᴱᴺᴬᴵ
根据您提供的信息，出现这个错误可能是因为 SaleDate 字段中有部分数据不符合 %M %d, %Y 格式。

您可以执行以下语句，找出不符合格式的数据，以便进行修正：

SELECT SaleDate FROM house WHERE STR_TO_DATE(SaleDate,"%M %d, %Y") IS NULL;

该语句会返回所有不符合 %M %d, %Y 格式的 SaleDate 值。根据返回结果，可以检查数据是否确实不符合格式，或者确定正确的日期格式。

另外，您提到有一种日期格式是 %d-%M-%Y，但是您的 STR_TO_DATE 函数中使用的却是 %M %d, %Y 格式。如果您需要将这种格式的日期也转换成日期类型，应该使用以下格式：

STR_TO_DATE(SaleDate, "%d-%M-%Y")

最后，您可以尝试在 STR_TO_DATE 函数中使用 TRY_CAST 函数来转换日期，该函数如果无法转换日期，会返回 NULL，避免了报错的问题：

SELECT TRY_CAST(SaleDate AS DATE) AS SaleDateConverted FROM house;

希望这些信息能够帮助您解决问题。

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
根据报错信息，可知出现了转换错误，因为指定的日期格式不符合字符串 SaleDate 的实际格式。因此，我们需要根据 SaleDate 字符串的实际格式指定正确的日期格式来进行转换。

对于第一种日期格式 "%M %d, %Y"，我们可以使用如下的 SQL 代码进行转换：

UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%M %d, %Y') WHERE SaleDate REGEXP '^[[:alpha:]]';

这里我们使用了正则表达式来匹配 SaleDate 字符串是否以字母开头，只有以字母开头的才是第一种日期格式。如果不使用正则表达式匹配，转换会失败。

对于第二种日期格式 "%d-%M-%Y"，我们可以使用如下的 SQL 代码进行转换：

UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%d-%M-%Y') WHERE SaleDate NOT REGEXP '^[[:alpha:]]';

这里我们同样使用了正则表达式匹配，只有 SaleDate 不以字母开头才是第二种日期格式，否则会认为是第一种日期格式，导致转换错误。

注意：以上代码只适用于 MySQL 数据库。

对于报错问题，我们可以大致了解一下，原因是将 '10-Jun-14' 这个字符串，按照 '%M %d, %Y' 的日期格式转换，但是 'Jun' 不符合 '%M' 的格式，导致转换失败。因此，我们需要根据实际情况来指定正确的日期格式进行转换。
如果我的回答解决了您的问题，请采纳！

参考GPT和自己的思路：1411错误通常表示日期或时间格式不正确，导致MySQL无法解析。在您的情况下，可能是因为日期格式的月份缩写与指定的格式字符串不匹配。 "%M"是用于解析月份的指令，但是如果使用的是缩写，例如Jun，则应该使用"%b"指令。请尝试以下更改：

select SaleDate, STR_TO_DATE(SaleDate,"%d-%b-%y"), SaleDateConverted from house;

update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%d-%b-%y")

如果上述更改仍无法解决问题，请检查表中的所有日期格式是否正确。

加一层转换:

SELECT DATE_FORMAT(STR_TO_DATE('10-Jun-14', "%d-%M-%Y"), "%M %d, %Y")

你的报错是因为 "%M" 是解析英文月份名称的，而 "Jun" 不是一个有效的月份名称，应该使用 "%b" 代替 "%M"，下面是正确的转换语句：

UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%M %d, %Y') WHERE SaleDate REGEXP '[a-zA-Z]+ [0-9]+, [0-9]+';

对于第二种日期格式，你需要使用 "%d-%b-%Y" 的格式，下面是正确的转换语句：

UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%d-%b-%Y') WHERE SaleDate REGEXP '[0-9]+-[a-zA-Z]+-[0-9]+';

其中 REGEXP '[a-zA-Z]+ [0-9]+, [0-9]+' 表示 SaleDate 是 "%M %d, %Y" 的格式，REGEXP '[0-9]+-[a-zA-Z]+-[0-9]+' 表示 SaleDate 是 "%d-%M-%Y" 的格式，只有符合相应格式的数据才会被转换，这样可以避免出现错误的转换。

这个错误出现的原因是STR_TO_DATE函数无法解析输入的日期字符串“10-Jun-14”，因为它的格式不符合给定的格式字符串"%M %d, %Y"。

为了解决这个问题，你需要调整格式字符串以匹配你的日期字符串。对于第二种格式（"%d-%M-%Y"），你可以使用"%d-%b-%Y"作为格式字符串。

以下是你可以尝试的代码：

-- 创建新的日期字段
ALTER TABLE house ADD SaleDateConverted DATE;

-- 转换第一种日期
UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%M %d, %Y') WHERE SaleDate REGEXP '^[A-Z][a-z]{2} [0-9]{2}, [0-9]{4}$';

-- 转换第二种日期
UPDATE house SET SaleDateConverted = STR_TO_DATE(SaleDate, '%d-%b-%Y') WHERE SaleDate REGEXP '^[0-9]{2}-[A-Z][a-z]{2}-[0-9]{4}$';

在这里，我们使用了REGEXP函数来验证日期字符串是否符合特定的格式。如果日期字符串匹配正则表达式，就会执行对应的更新语句。请确保你的日期字符串与你的正则表达式匹配，以避免出现错误。

有两个问题
1.因为同一个字段有两种格式，所以更新时需要区别对待。
2.格式参数设置有问题，应该用%b 替代%m
参数说明如下：
%b 三个字符缩写的月份名称，例如: Jan，Feb，Mar等
%M 月份全名称，例如: January, February…December
%m 以数值形式显示的月份名称，前导加0，例如: 01,02，… 12

正确的SQL如下：

update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%b %d, %Y") where SaleDate like '%,%';
update house set SaleDateConverted = STR_TO_DATE(SaleDate,"%d-%b-%Y") where SaleDate like '%-%';

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 给你找了一篇非常好的博客，你可以看看是否有帮助，链接：sql日期查询错误

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^