MySQL - 如何在 INSERT 语句中将字符串值解析为 DATETIME 格式?
我有一个 MySQL 数据库，其中有一列是日期类型 DATETIME.
I have a MySQL database, with a column that is date type DATETIME.
我从外部应用程序获取日期/时间的字符串值.该字符串值如下所示:
I am getting a string value for a date / time from an external application. That string value looks like this:
'5/15/2012 8:06:26 AM'
MySQL 在 INSERT 上抛出错误:"Error.日期时间值不正确".我的解决方法是将列类型更改为 VARCHAR，这可行，但我确实需要将数据作为正确的 Date &留待以后使用.
MySQL throws an error on the INSERT: "Error. Incorrect datetime value". My workaround was to change the column type to VARCHAR, which works, but I really need the data as a proper Date & Time for future use.
我研究了 MySQL DATETIME 值的可接受格式，发现 MySQL 希望 DATETIME 格式为 'YYYY-MM-DD HH:MM:SS'.
I researched accepted formatting for MySQL DATETIME values, and found that MySQL wants the DATETIME format as 'YYYY-MM-DD HH:MM:SS'.
我无法更改外部应用程序以重新格式化日期/时间字符串的格式，所以我唯一的机会就是处理它.
I can't change the external application to reformat the date / time string in a format, so my only chance is to deal with it.
我认为，我需要做的是在 INSERT 语句中使用 MySQL 语法 解析现有字符串，但我不确定如何去做.我有一些想法，我需要使用 SELECT 子句，也许是 STR_TO_DATE，以某种方式与我的 INSERT 语句结合使用.
What I need to do, I think, is parse the existing string, using MySQL syntax, inside of my INSERT statement, but I'm not sure how to do that. I have some idea that I need to use a SELECT clause, perhaps STR_TO_DATE, somehow in combination with my INSERT statement.
这是我当前的 INSERT 语句.我删除了其他不会导致问题的字段，只是为了使示例干净.
Here is my current INSERT statement. I removed the other fields that are not causing a problem, just to make the example clean.
INSERT INTO tblInquiry (fldInquiryReceivedDateTime) VALUES ('5/15/2012 8:06:26')
推荐答案
使用 MySQL 的 STR_TO_DATE() 函数来解析您尝试插入的字符串:
Use MySQL's STR_TO_DATE() function to parse the string that you're attempting to insert:
INSERT INTO tblInquiry (fldInquiryReceivedDateTime) VALUES
(STR_TO_DATE('5/15/2012 8:06:26 AM', '%c/%e/%Y %r'))
相关文章