#1
  1. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143

    updating time only in datetime field


    Hey--

    I need to change the time portion of my datetime fields, but keep getting syntax errors and I'm not sure why.

    I've tried Rudy's method, but get an error:

    #1064 - 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 '' at line 1

    My column:

    Code:
    classes

    The field I need to change:

    Code:
    date_time
    elsewhere on the interwebs, people have suggested something like:

    Code:
    UPDATE classes
    SET date_time=CONCAT(date(date_time), ' 12:00:00')
    WHERE date_time like '%12:10%'
    Still, the same error.

    All I really need to do is change the time from "12:10:00" to 12:00:00". This should affect ~200 records.

    Please...any suggestions will be rewarded with lavish amounts of praise.

    Thank you!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    15,971
    Rep Power
    9615
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,663
    Rep Power
    4288
    Originally Posted by Frank Grimes
    All I really need to do is change the time from "12:10:00" to 12:00:00".
    Code:
    SELECT CONCAT('old = ',date_time) AS old
         , CONCAT('new = ',date_time - INTERVAL 10 SECOND) AS new
      FROM yourtable
     WHERE TIME(date_time) = '12:10:00'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143
    Originally Posted by r937
    Code:
    SELECT CONCAT('old = ',date_time) AS old
         , CONCAT('new = ',date_time - INTERVAL 10 SECOND) AS new
      FROM yourtable
     WHERE TIME(date_time) = '12:10:00'

    Thanks, but how does this work as an UPDATE?

    Code:
    UPDATE classes set date_time=DATE_SUB(date_time, interval - 10 minute)
    That gives me an error:

    #1064 - 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 '' at line 1
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  8. #5
  9. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143
    Originally Posted by requinix
    What's the exact query you tried?
    I've tried several, including the one in my OP, but these as well:

    Code:
    UPDATE classes set date_time=DATE_SUB(date_time, interval - 10 minute)
    UPDATE classes SET date_time = CONCAT_WS(' ',DATE(date_time), '12:00:00')
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  10. #6
  11. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143
    Okay, seeing I don't need the - in the INTERVAL, this still doesn't work:

    Code:
    UPDATE classes
    SET date_time =
    DATE_SUB(date_time, INTERVAL 10 MINUTE)
    WHERE date_time LIKE '%12:10%'
    I get this very bizarre error:

    #1054 - Unknown column 'date_timeINTERVAL10MINUTE' in 'field list'
    Last edited by Frank Grimes; June 11th, 2016 at 09:13 AM.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  12. #7
  13. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    15,971
    Rep Power
    9615
    What are you running these queries through? It seems to be manipulating them.

    Comments on this post

    • Frank Grimes agrees : Looks like I'm out rep points, but you were right about bug!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,663
    Rep Power
    4288
    Originally Posted by Frank Grimes
    That gives me an error:
    try it without the DATE_SUB function, just like i did in the SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,663
    Rep Power
    4288
    Originally Posted by Frank Grimes
    Code:
    WHERE date_time LIKE '%12:10%'
    hey frank, don't use string functions on datetime datatypes

    did you see my SELECT query?

    Comments on this post

    • Frank Grimes agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143
    Originally Posted by requinix
    What are you running these queries through? It seems to be manipulating them.
    PHPMyAdmin
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  20. #11
  21. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143
    Originally Posted by requinix
    What are you running these queries through? It seems to be manipulating them.
    GR. I'm using PHPMyAdmin and using the Simulate Query button before committing. That errors out. Just running this query:

    Code:
    UPDATE classes set date_time=date_time - interval 10 minute
    It works without error. You're right. PHPmA was messing with it.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  22. #12
  23. Plays with fire
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    1,108
    Rep Power
    143
    Originally Posted by r937
    hey frank, don't use string functions on datetime datatypes

    did you see my SELECT query?
    Yeah, you're right.

    Thank you both for the help!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo