Results 1 to 8 of 8
  1. #1

    Mysql version incompatibility - STR_TO_DATE - Any way arount this?

    I wrote this script for a site and it was working fine, but now i have moved the site to a new host. every thing else is fine but when i run this update query i get the error below.


    $date = date ("d-m-Y H:i");

    $result = mysql_query("UPDATE table SET name = '$name', date = '$date', newdate=STR_TO_DATE('$date', '%d-%m-%Y %H:%i:%s'), aproved = '$aproved' WHERE id='$id'") or die(mysql_error());



    this is the error i get, i think its to do with STR_TO_DATE

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('04-06-2005 12:32:33', '%d-%m-%Y %H:%i:%s')


    how can i fix it?
    maybe i need to convert the date into newdate, but wirthout using STR_TO_DATE ?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,715
    STR_TO_DATE is 4.1 and onward, so few hosts will run it for now.

    You can use the PHP strtotime() function for a similar effect. It's not clear why you need $date in both a DATETIME format and the original format, though.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  3. #3
    Join Date
    May 2004
    Location
    Lansing, MI, USA
    Posts
    1,548
    You may have moved MySQL versions, which would explain the trouble.

    though if you change your format to:

    YYYY-MM-DD HH:MMS with HH being 24 hour format, you will be in ISO format and mysql will understand.

    So something like this might help...

    $date_time = split(' ', $date);
    $dates = split('-', $date_time[0]);
    $mysqldate = $dates[2].'-'.$dates[0].'-'.$dates[1].' '.$date_time[1];

    Give it a shot?
    Jacob - WebOnce Technologies - 30 Day 100% Satisfaction Guarantee - Over 5 Years Going Strong!
    Website Hosting, PHP4&5, RoR, MySQL 5.0, Reseller Hosting, Development, and Designs
    Powered By JAM - Professional Website Development - PHP, MySQL, JavaScript, AJAX - Projects Small & Large

  4. #4
    well the DATETIME format is to show data in date order

    and the other is to actually echo on the page, as people are more used to seing dates in that format.

    i know i could do it php and just have one field, but i'm stuck with it for now.

  5. #5
    thanks guys

    i'm gonna give these a shot now and see how it goes.

    strtotime()

    and

    $date_time = split(' ', $date);
    $dates = split('-', $date_time[0]);
    $mysqldate = $dates[2].'-'.$dates[0].'-'.$dates[1].' '.$date_time[1];

  6. #6
    Join Date
    May 2004
    Location
    Lansing, MI, USA
    Posts
    1,548
    I think strtotime might do it find in conjuction with a date(), but just to be on the safe side and to avoid testing time, i did the manual conversion code for you.
    Jacob - WebOnce Technologies - 30 Day 100% Satisfaction Guarantee - Over 5 Years Going Strong!
    Website Hosting, PHP4&5, RoR, MySQL 5.0, Reseller Hosting, Development, and Designs
    Powered By JAM - Professional Website Development - PHP, MySQL, JavaScript, AJAX - Projects Small & Large

  7. #7
    WebOnce should it matter if the field type of the field being updated is datetime

    i used you code and it looks fine(i mean it ads the right data in the field but the rows updated with that query don't show on my page) but if i manually add the exact same data in the field using phpmyadmin, it works.

    any ideas?

  8. #8
    Join Date
    May 2004
    Location
    Lansing, MI, USA
    Posts
    1,548
    Not really... if mysql can auto-convert the data, more power to it !
    Jacob - WebOnce Technologies - 30 Day 100% Satisfaction Guarantee - Over 5 Years Going Strong!
    Website Hosting, PHP4&5, RoR, MySQL 5.0, Reseller Hosting, Development, and Designs
    Powered By JAM - Professional Website Development - PHP, MySQL, JavaScript, AJAX - Projects Small & Large

Posting Permissions

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