Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Cool How do I update DateTime?

    I am trying to update the date and time in a table that I have.

    When I try to update it, the value in the MySQL database just gets reset to 00/00/00 00:00:00.

    Here is the code I'm using in PHP:
    $sqlCommand =

    "UPDATE Auction SET
    endDateTime = '11/01/2009 13:23',
    WHERE auctionID = '$auctionID'";

    return mysql_query($sqlCommand, $this->connection);

    Why does this not work?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I believe you need to include seconds. Though, MySQL is very flexible with its acceptance of various datetime formats, as can be seen in the manual here:

    http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    But I did not see where MySQL accepts omission of seconds.

    If you code error checking in your script you'll probably be told exactly what is wrong with the query.

  • #3
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for the link. This works:

    $sqlCommand = "UPDATE Auction SET endDateTime = '2009-11-01 13:23:00' WHERE auctionID = '1'";

    But the date has to be in YYYY-MM-DD format.

    How can I insert the date in this format:
    DD/MM/YYYY (the delimiter is / and the day and month comes before the year)?

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You'll need to reformat your string.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    You can also use a number, instead of a string, if that's easier for you:
    Code:
    $sqlCommand = "UPDATE Auction SET endDateTime = 20091101132300 WHERE auctionID = '1'";
    Is auctionID *REALLY* a TEXT field??? If it's numeric, then you should NOT put '...' around the 1 value.

    In any case, you'll need to use PHP code to generate the year-first format, either as a number or string.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    OldPedant, you do not need to use PHP to generate the date in requested format, you can do it with DATE_FORMAT directly in mysql query.

    Zachbb, make sure your date and time is in a DATETIME field type. that way you have access to all date and time functions for that field. you can use the DATE_FORMAT function to output the data in any number of formats.

  • #7
    New Coder
    Join Date
    Feb 2009
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Ok thanks..

    Could you look at this thread
    http://www.codingforums.com/showthread.php?p=783947

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Would you explain how you could use DATE_FORMAT() to convert an *INCOMING* date of the wrong format to the form needed by MySQL????

    I guess I could see using STRING functions to do that, followed by a CONVERT call, perhaps. But unless you did that in a stored proc, you'd have to pass the date-as-string value in to the code at least 3 times.

    Anyway, I don't see how DATE_FORMAT(), which is designed to format an *existing* DateTime value (e.g., a column in a MySQL table) to a string. So far as I can tell--even after reading the docs through a half dozen times--it is not capable of doing the reverse, converting a string of a given format into a DATETIME value.

    What am I missing???

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    sorry STR_TO_DATE to format an incoming string. and it exists in mysql.

  • The Following 2 Users Say Thank You to guelphdad For This Useful Post:

    Old Pedant (02-20-2009), zachbb (02-24-2009)

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Well I will be *#&*!?!*@#'d

    In 2 years of working with MySQL I never saw that! I wrote a nice neat Java function (for JSP pages) to do the work.

    DOH on me and thank you!!


  •  

    Posting Permissions

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