need help with mysql query

[minadreapta]

Hello,

 

what i want to to is basically this:

i want all the domain names in my whmcs that have:

- extension is ".tld"

- registrar is "email"

- status is "active", "cancelled" or "expired"

- expiration date is like "2012-mm-dd"

 

if the above conditions are fulfilled, to move the expiration date from 2012-mm-dd to 2212-mm-dd (2 hundred years in the future).

 

is this possible?

 

thanks.

[WHMCS JamesX]

You can use the following as a starting point and modify as per your own needs.

 

SELECT id
FROM `tbldomains`
WHERE
   `domain` LIKE '%.tld'
   AND `registrar` = 'email'
   AND `expirydate` BETWEEN '2012-01-01' AND '2012-12-31'
   AND `status` IN ('Active', 'Expired', 'Cancelled')

 

You can use DATE_ADD to figure out what the new dates should be.

DATE_ADD(expirydate, INTERVAL 200 YEAR)

 

Try this...

SELECT id, DATE_ADD(expirydate, INTERVAL 200 YEAR) AS NewExpiryDate
FROM `tbldomains`
WHERE
   `domain` LIKE '%.tld'
   AND `registrar` = 'email'
   AND `expirydate` BETWEEN '2012-01-01' AND '2012-12-31'
   AND `status` IN ('Active', 'Expired', 'Cancelled')

Edited November 25, 2012 by WHMCS JamesX

[minadreapta]

that worked like a charm. thanks a lot.