问题描述
我有一个 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'))
这篇关于MySQL - 如何在 INSERT 语句中将字符串值解析为 DATETIME 格式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!