Recordset for a date range – what am I doing wrong?
Hi Ray, I have a diary system set up which allows users to add events to a calendar and then an email is issued 1 month before the event with further instructions (I use a cron job to load the Universal email page which sends to the recordset). However I also need to allow for 'latecomers' who add an event within a month of the event date so that they receive the email the day it's added. I thought I had this working but I've realised that it's actually selecting all the records between the date added and the event date, even if that date is more than one month away.
So for example, say the EventDate is 2022-04-03 and DateAdded is 2021-12-22 14:42:56 (timestamp). I want to return the record on 3rd March 2022 (1 month before the event).
If the EventDate was 2022-01-03 and DateAdded is 2021-12-22 14:42:56, it needs to return the record today because the event is less than 1 month away.
This is my current query:
SELECT * FROM calendar WHERE (calendar.EventDate = DATE_ADD(CURDATE(),INTERVAL 1 MONTH) OR calendar.DateAdded BETWEEN CURDATE() AND DATE_ADD(calendar.EventDate, INTERVAL 1 MONTH))
- this is returning the first example even though the event is more than 1 month away.
I have to make it 'between' today and the event date so that it doesn’t return the same record every day up until the event date, which would cause the email to be sent every day. I’ve tried every possible combination and I think I’m now code-blind – can you see what I’m doing wrong?