Page 1 of 1

MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 4:32 am
by arunkar
Hi Guys,

I'm using MySQL 4.1.20

I need to add expiry date to the users table. The users table already has the joindate. The expiry date is 1 year from joint date. So I did the below SQL , which gives me error

Code: Select all

 
//registerDate                 datetime
//expiryDate                   datetime
 
 
Update jos_users set expiryDate= ADDDATE('Select registerDate from jos_users', INTERVAL 1 YEAR);
 
// OR
 
Update jos_users set expiryDate= DATE_ADD('Select registerDate from jos_users', INTERVAL 365 DAY);
 
// both the above SQL statements give me this optput...
(0 row(s)affected,372 warning(s))
(0 ms taken)
 
 
Any suggestions guys to add one year by using the existing datetime in the table.

Thanks

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:04 am
by VladSun
First, it's not the right way to use a subselect - where did these quotes come from?
Second, you don't need to use a subselect, because it's just another field in the same row.
Lets have this table:

mytable

Code: Select all

value1  |   value2
0       |   1
2       |   3
4       |   5
and the query:
[sql]UPDATE mytable SET value2 = value2 + value1 + 1[/sql]

Then the result:

Code: Select all

value1  |   value2
0       |   2
2       |   6
4       |   10

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:13 am
by adroit
Hi Friend,

Use this query

update jos_users set expiryDate= DATE_ADD(expiryDate, INTERVAL 1 YEAR);

Regards,
Pravin

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:23 am
by VladSun
adroit wrote:Hi Friend,

Use this query

update jos_users set expiryDate= DATE_ADD(expiryDate, INTERVAL 1 YEAR);

Regards,
Pravin
:)
First, it's not going to work.
Second - do you think that giving adroit copy-paste examples is good for him?

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:27 am
by adroit
Hi Friend,

Can you please let me know why this will not work.
Thanks in advance.

Regards,
Pravin

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:30 am
by VladSun
expiryDate has no value (i.e. NULL or 0000-00-00) before executing your query ;)

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:38 am
by adroit
Hi,

Oh I didn't saw the registerDate

May this will work

update jos_users set expiryDate= DATE_ADD(registerDate, INTERVAL 1 YEAR);

Regards,
Pravin

Re: MySQL ADDDATE() or DATE_ADD

Posted: Wed Oct 08, 2008 5:47 am
by arunkar
Thanks VladSun and adroit.

Now I know I shouldn't use subselection for the same row...

thanks again guys... :)