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
Thanks, Tim
Hello Tim,
Could you send a sample of a date-time field as you it is shown in your emails? I will write for you a script action that does this conversion.
Could you send a sample of a date-time field as you it is shown in your emails? I will write for you a script action that does this conversion.
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>');
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%>');
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%>');
Thanks for posting the solution Tim 

- All times are UTC -
