MySQL - 如何在 INSERT 语句中将字符串值解析为 DATETIME 格式? [英] MySQL - How to parse a string value to DATETIME format inside an INSERT statement?

问题描述

我有一个 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 格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！