Creative Juice Bo. Co. Presents

Taming the MySQL Date_Format Function

This is just a quick post of a little problem I've been running into lately. I'm not sure if it's a bug or some kind of intended MySQL thing, but it's annoying as heck. Also, I'm using MySQL version 5.0.32. I'm not sure if this applies to older versions of MySQL or not, but it seems to be something new with the 5.0 variants.

The problem stems from working with date objects and trying to format them using DATE_FORMAT(). Let's assume we have a database table that has two datetimes, '2009-12-12 15:30:35' and '2009-12-12 23:03:11'. We want to format the dates as MONTH-YEAR. Our query might look something like this:

<cfquery name="getDates" datasource="myDSN">
   SELECT DATE_FORMAT(my_date,"%m-%Y") as my_formated_dates
   FROM my_table
   ORDER BY my_date DESC
</cfquery>

If I do a CFDUMP of the data, I get:

+-------------------|
| my_formated_dates |
+-------------------+
| 1. binary         |
| 49504750484857    |
+-------------------+
| 2: binary         |
| 49504750484857    |
+-------------------+

See what it's doing there, it's treating my datetime objects as BINARY data. Again, I'm not sure if this is a bug or if it's meant to act that way, but for whatever reason the DATE_FORMAT() function is not returning a STRING object like you would expects. I can't even speculate what's going on, it's either the DATE_FORMAT function or the DATE object that's gone all wacko on us.

The good news, is that I did come across a fix. It's fairly simple really, we just need to convert the result to a STRING. Like so:

<cfquery name="getDates" datasource="myDSN">
   SELECT CONVERT(DATE_FORMAT(my_date, '%m-%Y') USING latin1) AS my_formated_dates
   FROM my_table
   ORDER BY my_date DESC
</cfquery>

And now if we do a CFDUMP we get what we would expect:

+-------------------|
| my_formated_dates |
+-------------------+
| 1. 12-2009        |
+-------------------+
| 2: 12-2009        |
+-------------------+

Hope this helps anyone else experiencing this problem. And if you have any insight to whats going on, please share!