Okay...so to tag onto the question I asked yesterday. I've got a query which uses CAST. Here's the query:
I've got a table on our server (mySQL 4.0.17 for Linux). When I run the above query it returns this:
SELECT DISTINCT CAST(DATE_FORMAT(sched_start, '%M %Y') AS CHAR) AS sched_start, CAST(DATE_FORMAT(sched_start, '%Y-%m') AS CHAR) AS sort
May 2005 2005-05
November 2005 2005-11
April 2005 2005-04
July 2005 2005-07
August 2005 2005-08
When I tried to run a similar query (same column types) on a different table I get an error:
You have an error in your SQL syntax near '(DATE_FORMAT(sched_start, '%M %Y') AS CHAR) AS sched_start, CAST(DATE_FORMAT(sch' at line 1
So, just to test, I copied the entire table from the remote machine to my machine and ran the query exactly as it was written. Remote works fine, local machine gives the same error.
Does anyone know what's going on? Do I just need to upgrade mySQL? I can't see a minor point release causing this issue, but then I'm no DBA or tech guy. I just want this to work.
Anyone have any ideas? I need help badly.