MySQL - How to parse a string value to DATETIME format inside an INSERT statement?

mysql datetime insert
Tim Murphree picture Tim Murphree · May 17, 2012 · Viewed 139k times · Source

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')

Thank you for any help, I am a SQL newbie.

Answer

eggyal picture eggyal · May 17, 2012

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'))

Related questions

Object of class DateTime could not be converted to string

I have a table with string values in the format of Friday 20th April 2012 in a field called Film_Release I am looping through and i want to convert them in datetime and roll them out into another table. My …

Read More
MySQL: Insert datetime into other datetime field

I have a table with a DATETIME column. I would like to SELECT this datetime value and INSERT it into another column. I did this (note: '2011-12-18 13:17:17' is the value the former SELECT gave me from the …

Read More
How do you set a default value for a MySQL Datetime column?

How do you set a default value for a MySQL Datetime column? In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.

Read More