Continuing my cast problems - mysql.4.0.0 alpha

[creole]

Okay...so to tag onto the question I asked yesterday. I've got a query which uses CAST. Here's the query:

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
FROM tblSchedule 

I've got a table on our server (mySQL 4.0.17 for Linux). When I run the above query it returns this:
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.

[r937]

you don't need to CAST to CHAR

DATE_FORMAT by definition produces a string

p.s. i'm no DBA either

[longneck]

CAST() and CONVERT() are available as of MySQL 4.0.2. The CHAR conversion type is available as of 4.0.6. The USING form of CONVERT() is available as of 4.1.0.

if you are going to be using a 4.0 series mysql, you absolutely need to be on 4.0.16 or later for security reasons.

but if you are upgrading, you should upgrade to 5.0 instead as 4.0 is depreacted, and 4.1 is about to fall off the support track.

[creole]

longneck...

Thanks for the update. I'm running that version for dev only. Our remote is 4.0.17. I'd love to upgrade to 5 but apaprently there's some major compatibility issues with ColdFusion 6.1, so no joy for creole for the time being.

By the way, where in Tampa are you located? I lived in Brandon for 15 years.

Rudy...

Forgot to mention that for some unknown reason when I run the query without the cast function, ColdFusion returns binary characters instead of a formatted string. When I run the query in phpmyadmin it works just fine, but for some reaons CF can't understand the resultset.

Any ideas?

[r937]

nope, no ideas, maybe re-install coldfusion?

ha ha ha -- that was a joke, eh

[creole]

I'm laughing.

[longneck]

i would upgrade your dev to 4.0.17 to match your production environment. that might even fix it so you don't need the cast() hack.

i work on rocky point and live in town'n'country. i need to move back to chicago! i can't stand 95 degrees and 95% humidity!