MySQL - 如何在 INSERT 语句中将字符串值解析为 DATETIME 格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10636152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - How to parse a string value to DATETIME format inside an INSERT statement?
提问by Tim Murphree
I have a MySQL database, with a column that is date type DATETIME.
我有一个MySQL database, 带有日期类型的列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 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 在 INSERT: 上引发错误"Error. Incorrect datetime value"。我的解决方法是将列类型更改为VARCHAR，这可行，但我确实需要将数据作为正确的日期和时间以供将来使用。
I researched accepted formatting for MySQL DATETIMEvalues, and found that MySQLwants the DATETIMEformat as 'YYYY-MM-DD HH:MM:SS'.
我研究接受格式化MySQL DATETIME值，发现MySQL希望DATETIME的格式'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.
我无法更改外部应用程序以重新格式化日期/时间字符串的格式，因此我唯一的机会就是处理它。
What I need to do, I think, is parse the existing string, using MySQL syntax, inside of my INSERTstatement, but I'm not sure how to do that. I have some idea that I need to use a SELECTclause, perhaps STR_TO_DATE, somehow in combination with my INSERT statement.
我认为我需要做的是解析现有的字符串，使用MySQL syntax, 在我的INSERT语句中，但我不知道该怎么做。我有一些想法，我需要使用一个SELECT子句，也许STR_TO_DATE，以某种方式与我的 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 语句。我删除了没有引起问题的其他字段，只是为了使示例干净。
INSERT INTO tblInquiry (fldInquiryReceivedDateTime) VALUES ('5/15/2012 8:06:26')
Thank you for any help, I am a SQLnewbie.
感谢您的帮助，我是SQL新手。
回答by eggyal
Use MySQL's STR_TO_DATE()function to parse the string that you're attempting to insert:
使用 MySQL 的STR_TO_DATE()函数来解析您尝试插入的字符串：
INSERT INTO tblInquiry (fldInquiryReceivedDateTime) VALUES
(STR_TO_DATE('5/15/2012 8:06:26 AM', '%c/%e/%Y %r'))
