MySQL - How to parse a string value to DATETIME format inside an INSERT statement?
datetime, insert, mysql
Solution
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'))
Problem
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 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. 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. 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. 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') ```