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

Question

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  amp  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

User · Accepted Answer

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] MySQL - How to parse a string value to DATETIME format inside an INSERT statement?

Examples related to mysql

Examples related to datetime

Examples related to insert