Thoughts from the mountains

Today, I had to create charts from percentage values stored as integers in a database. The idea was to create a bar or pie chart that would show the number of results in different ranges of percentages. The data was collected through a text input that allowed integer values between 0 and 100. I used the sql CASE operator select counts of the number of responses in each desired range.

The MySQL error, Data truncation: Incorrect datetime value, is rather obvious and is caused by trying to insert a non datetime value into a datetime field in MYSQL 5.1. I was surprised to find the same code would run without error on MySQL 4.1. Apparently, earlier versions of MySQL would convert incorrect datetime values into 0000-00-00 00:00:00 and proceed without error. It seems that throwing the error is a better option to alert the developer of a problem if they are attempting to insert a non datetime value into a datetime value field.

I ran into a issue today due to a difference between MySql 4.1 and 5.1, or at least that is what I'm currently thinking. We have a ColdFusion template that is running as a scheduled task to determine new and updated records in a table on one server and then either updates or creates new records on a second server at a different location. Pretty straight forward, and it has worked well for the last several years.

I'm currently moving a site from MySQL 4 and ColdFusion 7 on IIS 6 to MySQL 5 and ColdFusion 9 on IIS 7. One of the first problems that I noticed was the following error on one of my database driven templates:

Cannot convert value ‘0000-00-00 00:00:00? from column X to TIMESTAMP.