Expire within 30 days

[craigeves]

Hi again

 

Can someone please help?

 

I want to show all promotions that are set to expire within 30 days. This is what I have:

SELECT promotions.promo_id, promotions.ret_id, promotions.title, promotions.`desc`, promotions.terms, promotions.publish, promotions.expiry
FROM promotions
WHERE promotions.expiry <= DATE_ADD( day ,30 , GETDATE() ) 

 

but it returns the following error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30 , GETDATE() )' at line 1

 

Can someone help?

 

Thanks in advance

[AdRock]

I think you may be getting a problem with the field "desc"

 

I had the same problem with that as DESC is a resevered word.  Try changing it to description

[craigeves]

OK - I got it working using:

 

SELECT promotions.promo_id, promotions.ret_id, promotions.title, promotions.desc, promotions.terms, promotions.publish, promotions.expiry
FROM promotions
WHERE promotions.expiry <= DATE_ADD( now(), INTERVAL 1 MONTH) 

 

but for some reasons it returns results that are already expired also. can anyone tell me why???

 

Thanks

 

Craig

 

[TapeGun007]

I don't know if this will help you at all, as it appears you are looking really for an SQL solution, not a php solution.  Which, if that is the case, you may wish to post in that section of the forum instead.

 

Here is a piece of code I used on a website to put New Members (30 days old or less) into a different listing:

 

/* Set a date back 30 days ago */
$date = date("m/d/Y");
$newdate = strtotime ( '-30 day' , strtotime ( $date ) ) ;
$newdate = date ( 'm/d/Y' , $newdate );
echo $newdate;
/* $newdate is now 30 days in the past, new accounts cannot be more than 30 days old */

 

Then you could do something like WHERE promotions.expiry > $newdate

[MatthewJ]

Or, all in one step

 

$newdate = date('m/d/Y', time() - (86400 * 30));

[PFMaBiSmAd]

You are asking for dates that are less-than or equal to the result of the date_add(), which will be all the dates that are less-than or equal to one month from the current date/time -

WHERE promotions.expiry <= DATE_ADD( now(), INTERVAL 1 MONTH) 

 

What exactly do you want to match, relative to the current date?

 

Also, NOW() is a date/time value. What type of values are in expiry? If you want to compare just the date parts, use CURDATE() instead of NOW().

[ignace]

WHERE promotions.expiry > now()

 

Returns all promotions that are set to expire in the future