Calling All MySQL Geeks
I'm going bald, and it's all MySQL's fault... (at least I hope it is).
I'm having major issues with MySQL returning dates in Binary format, versus the format that I give them. Let's take the following code for example:
DATE_FORMAT(tbl_orders.order_Date,'%Y-%m-01')
Now any sensible person would expect that to return "2003-12-01" right? Well, MySQL doesn't seem to want to be sensible, because it's returning this in my CFDUMP: binary - 50484851454854454849
If anyone has an idea of why the heck this is happening, I'd be more than happy to listen and try something else. I've tried using EXTRACT and piecing together a string, I've tried using STR_TO_DATE, I've tried yelling and screaming and calling it names, and nothing seems to be behaving correctly, and noone seems to know the answer.
Leave a comment if you have any idears :)
4 Comments
Daniel Short wrote on 02/03/04 1:38 PM
eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeew, that's not purty. i'll certainly keep ya updated :)
Andy Jarrett wrote on 02/04/04 1:39 AM
SELECT DATE_FORMAT(tblName.yourDate,'%Y-%m-01') FROM tblName Works fine for me 4.0.13 Otherwise tryout SELECT CONCAT(DATE_FORMAT(tblName.yourDate,'%Y-%m'),'-01') FROM tblName
Daniel Short wrote on 02/04/04 1:39 AM
I'm afraid I get the exact same result, 50484851454854454849, using .15...
Brendan Sisson wrote on 02/03/04 1:38 PM
I'm not a MySQL guru but have come across this before and took the easy way out. Loop back over your query and convert the binary date to a string. eg: <cfquery name="qGetDateQuery" datasource="myDsn"> select myDate from myTable </cfquery> <!--- myDate comes out of the query as binary, so convert it back to being a string ---> <cfloop query="qGetDateQuery"> <cfset qGetDateQuery.myDate = tostring(qGetDateQuery.myDate)> Hope that helps and if you ever find a more elegant solution please let me know :) -Brendan http://farcry.daemon.com.au