Add A Year To DATETIME Value

[CrYpTiC_MauleR]

I have the following value in a datetime field 2007-12-10 00:00:00

I want to add a year to it using MySQL so it is 2008-12-10 00:00:00

How would this be done? I came across PERIOD_ADD() and DATE_ADD() but didn't work for me, not sure if they can be used in an UPDATE query. Thanks!

[VGR]

again, INTERVAL is your friend

"+ INTERVAL 1 YEAR"

[CrYpTiC_MauleR]

I didn't have a + sign will try the query again with it.

[CrYpTiC_MauleR]

Didnt work =o(

Code

Language: sql (GeSHi-highlighted)
$sql = 'UPDATE `counter` '
       . "SET `expires` = DATE_ADD(`expires`, + INTERVAL 1 YEAR) "
       . 'WHERE `user` = ' . USERNAME . ' '
       . 'LIMIT 0, 1';

its 'expires' field is a 'datetime'
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[VGR]

this should be better :

$sql = 'UPDATE counter'
       .' SET expires = expires + INTERVAL 1 YEAR'
       .' WHERE user = ' .USERNAME
       .' LIMIT 0, 1;';

of just $sql = 'UPDATE counter SET expires = expires + INTERVAL 1 YEAR WHERE user = '.USERNAME.' LIMIT 0, 1;';

I'm not sure USERNAME is a valid variable ?!? (could be a defined constant, so I half-close my mouth ;-)

[CrYpTiC_MauleR]

oops problem was the LIMIT clause, not allowed with UPDATE, also was using the mysql account for reading data not writing so permissions were wrong. =oP Man its the stupid things in life that gets to you.

[VGR]

I didn't see it either, but for a good reason : LIMIT is allowed in UPDATE ;-)
http://dev.mysql.com/doc/refman/4.1/en/update.html

your problem was more likely the user not having the UPDATE privilege ;-)

[CrYpTiC_MauleR]

weird tried LIMIT with the update privs and still didn't work.

[VGR]

check which MySqld version you have

it may be that only "LIMIT n" is allowed and not "LIMIT n,m" but I didn't check the documentation a second time.

LIMIT on updates and deletes is very useful, especially when you've doublons and want to amend/delete only one of the two :D