0

I'm having a bit of trouble currently. The system I'm trying to implement is a 'callback' system that will set callbacks against customers that will pop up on the system at the specified date.

One of the features is a reoccurring callback. The two options are monthly and yearly. I have the yearly ones working fine:

SELECT * FROM `callbacks` WHERE (
-- Yearly Callbacks
(DAYOFYEAR(`date_callback`) = DAYOFYEAR(NOW()) AND `occurs` = 'yearly') OR

-- Other Callbacks For Today
(DATE(`date_callback`) = DATE(NOW())))

But I'm having trouble visualising how to check if a callback that is monthly is due. It should only occur once every 30 days in the list (that uses the above query.) I was using DAYOFMONTH but I realised that if a callback was set for the 31st it wouldn't appear in the list for a few months at a time despite it being a monthly callback.

So if one is set for March 3rd 2013 and set for montly, it should appear in the results of the query if the date is:

  • March 3rd 2013
  • April 1st 2013
  • May 1st 2013
  • May 31st 2013
  • June 30th 2013

and so on. Has anyone dealt with this before?

andy
  • 2,369
  • 2
  • 31
  • 50

1 Answers1

1

I'd consider to use interval, something like

. . . (DATE_ADD(NOW(), INTERVAL 1 MONTH)) . . .

See also this post.

Or - you can try (pseudocode)

now() - pre_saved_date % 30 (module of 30)

For % - use MOD function, like 

select MOD(2, 3);
select 3 % 2;
Community
  • 1
  • 1
evgenyl
  • 7,837
  • 2
  • 27
  • 32
  • That's not really what I need. That'll only give me one month in advance. I need it so it will occur in that query if it is within a monthly period of the given date. – andy Apr 02 '13 at 09:45
  • @andy - did you figure it out? – evgenyl Apr 02 '13 at 11:35