Hello i have two columns: duration (int) and dateCol (date) in a table. How should i proceed if i want to know when CURDATE() - dateCol will iqual to duration.
ie: CURDATE() - dateCol (2010-05-08) = duration (14 days), and then send an e-amil?
DAYS won't work. You mean DAY.
Mark Byers
2010-05-22 19:03:18
+1 after the fix.
Mark Byers
2010-05-22 19:12:16
Thanks. Both solutions (the second one from Mark Byers) work.
jartaud
2010-05-22 19:38:20
+2
A:
To do what you want you can use DATEDIFF:
DATEDIFF(NOW(), last_email_date) >= 14
An alternative method is to interval subtraction:
CURDATE() - interval 14 day >= last_email_date
The second can be faster if there is a suitable index.
Note that I've chosen to use an inequality test instead of an equality because this will handle missed emails if your process is offline for one day and is restarted the next day. You should mark the row as handled when you've handled it to prevent more than one email being sent. This will also prevent multiple emails being sent if your process runs more than once per day (this could happen if you restart it to upgrade / bug fix).
Mark Byers
2010-05-22 18:57:48
Thanks. I think i ll use equality, cause i need the difference iqual 14.
jartaud
2010-05-22 19:32:06
@jartaud: That's OK. If you say that, it means that it's better to not send an email at all than to send it one day too late. If that's what you want, you should use an equality.
Mark Byers
2010-05-22 19:42:19
@Mark Byers, suppose we have dateCol iquals to:2010-05-18, 2010-05-08, 2010-05-10, ...(CURDATE() - interval 14 day > dateCol) AS TEST will return nothing, but TEST = 2010-05-08. (if CURDATE() is 2010-05-22).
jartaud
2010-05-22 20:09:01