Add 1 year to today's date

[[deleted]]

I am trying to create an expiration date for member's accounts. I want to add 1 year to todays date.

I tried using MySQL's date_add function directly in the Update statement, but the query did not work.

qUpdateUserStatus = mysql_query ("UPDATE tblUsers
SET password = PASSWORD('$newpass'),
ActivationKey = '$key',
ActivationDate = NOW(),
fk_StatusID = '$status',
ExpirationDate = date_add(NOW(), INTERVAL 1 Year)
WHERE ID = '$UserID'");

If I remove the line
ExpirationDate = date_add(NOW(), INTERVAL 1 Year)
the update works without a hitch.

Any ideas?

[cgraz]

aren't you missing a plus sign?

date_add(NOW() + INTERVAL 1 YEAR)

http://www.mysql.com/doc/en/Date_and_time_functions.html

Cgraz

[[deleted]]

I tried that as well. Still not updating the table.

[cgraz]

have yout ried putting single quotes around it?

qUpdateUserStatus = mysql_query ("UPDATE tblUsers
SET password = PASSWORD('$newpass'),
ActivationKey = '$key',
ActivationDate = NOW(),
fk_StatusID = '$status',
ExpirationDate = 'date_add(NOW(), INTERVAL 1 Year)'
WHERE ID = '$UserID'");

Cgraz

[[deleted]]

Yes, and still no luck.

[cgraz]

what's the mysql_error?

// I added in a dollar sign on qUpdateUserStatus
$qUpdateUserStatus = mysql_query ("UPDATE tblUsers
SET password = PASSWORD('$newpass'),
ActivationKey = '$key',
ActivationDate = NOW(),
fk_StatusID = '$status',
ExpirationDate = 'date_add(NOW() + INTERVAL 1 Year)'
WHERE ID = '$UserID'") or die(mysql_error());

Cgraz

[[deleted]]

With the single quotes around ExpirationDate = 'date_add(NOW() + INTERVAL 1 Year)'

I get no errors, but the ExpirationDate filed does not update. The other fields do.

Without the singles quotes I get the error:
You have an error in your SQL syntax near ') WHERE ID = '37'' at line 6

which, to me, means I need the single quotes.

[cgraz]

yeah, you def need the single quotes. This is an odd problem (probably because I've never run into it before). What happens if you try this

$qUpdateUserStatus = mysql_query ("UPDATE tblUsers 
SET password = PASSWORD('$newpass'), 
ActivationKey = '$key', 
ActivationDate = NOW(), 
fk_StatusID = '$status', 
ExpirationDate = 'NOW() + INTERVAL 1 YEAR' 
WHERE ID = '$UserID'") or die(mysql_error()); 

Cgraz

[[deleted]]

All the fields update except for the ExpirationDate.

[cgraz]

hmm, I really have no idea then. I'd have to play around with it to figure it out. Maybe someone else who has run into this problem can be of some assistance.

Cgraz

[[deleted]]

I got it to work by using

ExpirationDate = (NOW() + INTERVAL 1 Year)

[cgraz]

ah so the parenthesis did the trick? I didnt think you needed them since you had the single quotes.

Cgraz