I have done a lot of internet searches, and can't find a report of a problem quite like this one, so I'm hoping someone here might have an idea of what is going wrong. In my java code I have a query that is pulling data from the database. I have confirmed that the SQL is well-formed, and all of my other queries in this java file run fine. In fact, even this query runs as it should when I am using the dev test Tomcat server. However, when I try to run this particular query using the dev test Resin server (where it was working perfectly fine up until just recently), or the QA test server, which also uses Tomcat, I get this error:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '131500'
The '131500' I'm assuming is coming from the time portion of the first datetime value that is returned, which is '13:15:00'. The particular part of this query that is causing the problem is this line:
CONCAT(DATE_FORMAT((SELECT datenotime), '%Y-%m-%d'),' ',DATE_FORMAT((SELECT appointmenttime FROM appointments WHERE appointmentdate = datenotime AND appointments.service_id = service.id), '%T'))
If I remove this then I experience no problems on any server, so it only occurs when the time is formatted and concatenated. I would think that the issue here must be some server setting? Although there were no recent changes made to the Resin server that I know of. I tried updating Resin's msyql connector jar, but that didn't help. Does anyone know what is going wrong?
Thanks!
My guess is that you are getting this error when datenotime is null.
That would make sense, since you are concatenating datenotime and then appointmenttime. If datenotime is null, then all you have left is time, which simply can't be converted to any known datetime value.