SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Continuing my cast problems - mysql.4.0.0 alpha

    Okay...so to tag onto the question I asked yesterday. I've got a query which uses CAST. Here's the query:
    PHP Code:
    SELECT DISTINCT CAST(DATE_FORMAT(sched_start'%M %Y') AS CHAR) AS sched_startCAST(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.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you don't need to CAST to CHAR

    DATE_FORMAT by definition produces a string

    p.s. i'm no DBA either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html
    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.

  4. #4
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, no ideas, maybe re-install coldfusion?

    ha ha ha -- that was a joke, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm laughing.

    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •