Email Parser

Is there any way that the program can format the date & time fields of a parsed email in the format that MySQL requires in order to store it in the MySQL DATETIME data format? Right now, the date and time value that the program produces has AM, PM etc. in it, and so I have to store that as a string in a VARCHAR field instead of storing it properly.

Thanks, Tim

Hello,
Here's the error message in the log file:

[ 5/15/2012 8:06 AM ] Error. Incorrect datetime value: '5/15/2012 8:06:26 AM' for column 'fldInquiryReceivedDateTime' at row 1.

MySQL wants the DATETIME format as 'YYYY-MM-DD HH:MM:SS'.

Here's our SQL Insert statement, as seen in the log:

[ 5/15/2012 8:06 AM ] Running SQL statement
[ 5/15/2012 8:06 AM ] INSERT INTO tblInquiry
(fldInquiryReceivedDateTime, fldInquiryFromEmail, fldInquiryToEmail, fldInquiryEmailBCC, fkInquirySchoolID, fldInquiryTopic, fldInquiryComments, fldCC)
VALUES ('5/15/2012 8:06:26 AM','joe@testcompany.com', 'moreinfotest@sometestcomp.com', '', '6','This is a test subject','This is a test body.', '"\'Tim Lastname\'" <timm@testCCaddcompany.com>');

Found a solution to the problem above. Use MySQL STR_TO_DATE function inside your INSERT to re-parse the date into an acceptable format. See below:

INSERT INTO tblInquiry
(fldInquiryReceivedDateTime, fldInquiryFromEmail, fldInquiryToEmail, fldInquiryEmailBCC, fkInquirySchoolID, fldInquiryTopic, fldInquiryComments, fldCC)
VALUES ((STR_TO_DATE('<%DateTimeReceived%>','%c/%e/%Y %r')),'<%From%>', '<%To%>', '<%BCC%>', '6','<%Subject%>','<%Body%>', '<%CC%>');