Problem with date, membership expired but it isn't yeat

Question

Hi there,

I need to modify my valid notification script (which notify members, before membership expiration, on expiration day and so on), to work on specific way.

So my expires column in database (MySQL) is datetime type so value in it for example look like this: 2010-08-08 13:46:38

Here is a thing:

Script can be called from backend at any time and it expect from user to enter a number for example 1 which means that all members which are expiring tomorrow (at any time) should be notified. So if script is called at: 2010-07-08 00:00:05 all members which expiration date is from 2010-08-08 00:00:00 to 2010-08-08 23:59:59 should be notified. or in other words all members which expiration date contains 2010-08-08.

I am using ZendFramework for this but I think that I will handle a MySQL code as well. I also believe that u get what I mean by this post, and what I want to accomplish...

My code which is not working fine (for this approach) looks like this:

$d = new Zend_Db_Expr("DATE_ADD(NOW(), INTERVAL $days DAY)");

$days2 = $days-1;
$d2 = new Zend_Db_Expr("DATE_ADD(NOW(), INTERVAL $days2 DAY)");
return $this->fetchAll("status='1' AND expires <= $d AND expires >=$d2");

Answer 1

To get the start and end of tomorrow's time window how about using CURRENT_DATE() or CURDATE(), e.g.,

DATE_ADD(CURRENT_DATE(), INTERVAL $days DAY )
DATE_ADD(CURRENT_DATE(), INTERVAL $days2 DAY )

That will give you

+-------------------------------------------+-------------------------------------------+
| DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY ) | DATE_ADD(CURRENT_DATE(), INTERVAL 2 DAY ) |
+-------------------------------------------+-------------------------------------------+
| 2010-08-08                                | 2010-08-09                                | 
+-------------------------------------------+-------------------------------------------+

You would need to tweak your inequality slightly:

------- use less-than instead of less-than-or-equal.
expires < $d AND expires >=$d2