Print Page - Add A Year To DATETIME Value

Title: Add A Year To DATETIME Value
Post by: CrYpTiC_MauleR on March 04, 2007, 11:09:54 PM

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!

Title: Re: Add A Year To DATETIME Value
Post by: VGR on March 05, 2007, 12:24:50 AM

again, INTERVAL is your friend

"+ INTERVAL 1 YEAR"

Title: Re: Add A Year To DATETIME Value
Post by: CrYpTiC_MauleR on March 05, 2007, 11:33:17 AM

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

Title: Re: Add A Year To DATETIME Value
Post by: CrYpTiC_MauleR on March 05, 2007, 12:41:16 PM

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

Title: Re: Add A Year To DATETIME Value
Post by: VGR on March 05, 2007, 12:47:04 PM

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 ;-)

Title: Re: Add A Year To DATETIME Value
Post by: CrYpTiC_MauleR on March 05, 2007, 07:50:09 PM

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.

Title: Re: Add A Year To DATETIME Value
Post by: VGR on March 06, 2007, 12:21:36 AM

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 (http://dev.mysql.com/doc/refman/4.1/en/update.html)

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

Title: Re: Add A Year To DATETIME Value
Post by: CrYpTiC_MauleR on March 06, 2007, 03:06:28 PM

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

Title: Re: Add A Year To DATETIME Value
Post by: VGR on March 06, 2007, 03:27:37 PM

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

Experts Round Table Network

Databases => MySQL => Topic started by: CrYpTiC_MauleR on March 04, 2007, 11:09:54 PM