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?

I think you want:

SELECT * FROM calendar
WHERE
(calendar.EventDate = DATE_ADD(CURDATE(),INTERVAL 1 MONTH) AND calendar.DateAdded < DATE_ADD(calendar.EventDate,INTERVAL 1 MONTH))
OR
(calendar.DateAdded = CURDATE() AND calendar.EventDate BETWEEN CURDATE() AND DATE_ADD(calendar.EventDate, INTERVAL 1 MONTH))

Thanks for looking at this for me. Your suggestion returns the record when the event date is one month from today, but it doesn't work if the DateAdded is within one month of the EventDate. It seems to be the second where clause that doesn't work:

(calendar.DateAdded = CURDATE() AND calendar.EventDate BETWEEN CURDATE() AND DATE_ADD(calendar.EventDate, INTERVAL 1 MONTH))

So if I have an event on 2022-01-07 and I add it today, I want it to return the record but the above query doesn't. Not sure if it's relevant but EventDate is a Date field and DateAdded is a Timestamp.

Since it is a timestamp, it includes minutes and seconds, so the comparison for today would fail. I think you would want:

(DATE(calendar.DateAdded) = CURDATE() AND calendar.EventDate BETWEEN CURDATE() AND DATE_ADD(calendar.EventDate, INTERVAL 1 MONTH))

I thought that had solved the problem, but I'm still getting the record returned when the event is more than 1 month away. I've copied the full query in the PM and attached a screenshot of the record that's being returned. Much appreciated that you looked at this for me when we're so close to Christmas - I'm sending you a donation to buy a Christmas beer or two!

I decided to start from scratch... This is what I came up with:

(calendar.EventDate = DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
OR
((DATE(calendar.DateAdded) = CURDATE()) AND calendar.EventDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 MONTH)))

The logic is:
(calendar.EventDate = DATE_ADD(CURDATE(),INTERVAL 1 MONTH))

(the event starts exactly one month from today)

OR

((DATE(calendar.DateAdded) = CURDATE()) AND calendar.EventDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 MONTH)))

(the event was added today) AND the event is between today and one month from today

Works perfectly, thanks so much and have a great Christmas!