While working a contract at a small educational institution this past summer, Dave R. was asked to "clean up" some of the PHP code behind the institution's main web site. In particular, the IT manager asked if course listings couldn't be sorted so that courses appeared ordered by their starting dates.

The web site had been written by five or six different contractors over the past few years, and the most recent and capable of these had declared that it “was not feasible” to sort the course listings by date.

This seemed preposterous to Dave: the data was stored in a MySQL database; sorting should be an entirely feasible matter of appending an ORDER BY clause to a SELECT statement.

Tracing through the code, Dave found that the database table in question, COURSE, did indeed contain the starting date. But there was a hitch. The MySQL database was repopulated every few hours from the institution's main Oracle server. One of the earlier contractors had settled on Oracle's default date representation, DD-MMM-YY, for all date values transferred to MySQL. And, instead of using the DATE type for these columns, the contractor had gone with CHAR(9), essentially converting these dates into character strings.

Now, date strings in the DD-MMM-YY format do not sort properly: 14-NOV-07 will sort before 21-FEB-03 because 1 comes before 2 in pretty much every collating sequence known--month and year values be damned.

Unfortunately, converting the DD-MMM-YY dates into something that could be sorted turned out to be the infeasible part. MySQL offers a STR_TO_DATE() function that does the trick, but only on more recent versions than the one running on the institution's rickety old Solaris web server.

Yet another contractor had come up with a solution elsewhere in the code. To sort the contents of the COURSE table, the following query could be used.

SELECT * FROM course ORDER BY CONCAT('20', SUBSTR(start_date, 8, 2), '-', CAST(CASE SUBSTR(start_date, 4, 3) WHEN 'JAN' THEN '01' WHEN 'FEB' THEN '02' WHEN 'MAR' THEN '03' WHEN 'APR' THEN '04' WHEN 'MAY' THEN '05' WHEN 'JUN' THEN '06' WHEN 'JUL' THEN '07' WHEN 'AUG' THEN '08' WHEN 'SEP' THEN '09' WHEN 'OCT' THEN '10' WHEN 'NOV' THEN '11' ELSE '12' END AS CHAR), '-', SUBSTR(start_date, 1, 2));

Had the earlier contractor instead used the MySQL DATE type for the start_date column, the same query could've been written thus.

SELECT * FROM course ORDER BY start_date;

This not being an isolated example, Dave soon decided that it “was not feasible” to renew the contract.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!