Finding the date one month in advance on the same day

Question

In MySQL, I can do something like:

SELECT DATE_ADD('2010-07-02', INTERVAL 1 MONTH)

And that returns:

2010-08-02

That's great... now is there a MySQL function that I can use to find what the date would be one month in advance on the same day. For example, 7/2/2010 falls on the first Friday of July, 2010. Is there an easy way to find what the first Friday of August, 2010 is with an SQL statement?

Thanks in advance for your help!

Answer 1

It got a bit complicated, but here goes:

SELECT IF(MONTH(DATE_ADD('2010-07-02', INTERVAL 28 DAY)) = MONTH('2010-07-02'), 
  DATE_ADD('2010-07-02', INTERVAL 35 DAY),
  DATE_ADD('2010-07-02', INTERVAL 28 DAY));

Rationale: If you add 4 weeks or 5 weeks, you're still on the same day; since all months are between 28 and 35 days long, if 4 weeks later is still the same month, add another week.

UPDATE: Umm, I did not think this through very well - it works for first X in month, but necessarily for 2nd, 3rd... (i.e. 3rd X in month might return a 2nd X next month). Try #2:

SELECT IF(
  CEIL(DAY(DATE_ADD('2010-07-02', INTERVAL 35 DAY)) / 7) = CEIL(DAY('2010-07-02') / 7), 
  DATE_ADD('2010-07-02', INTERVAL 35 DAY),
  DATE_ADD('2010-07-02', INTERVAL 28 DAY));

Rationale: CEIL(DAY(x) / 7) is x's week number. If it's different when you add 5 weeks, then add 4 weeks.

UPDATE 2: LOL, I suck today, I should really think before I post... Week is usually defined as Mon-Sun, or Sun-Mon, not as from whatever started the month till 6 days later. To compensate for this:

SELECT IF(
    CEIL((DAY(DATE_ADD('2010-07-02', INTERVAL 28 DAY)) - DAYOFWEEK('2010-07-02')) / 7)
    = CEIL((DAY('2010-07-02') - DAYOFWEEK('2010-07-02')) / 7), 
  DATE_ADD('2010-07-02', INTERVAL 28 DAY),
  DATE_ADD('2010-07-02', INTERVAL 35 DAY));

Answer 2

Does this do the trick?

SELECT DATE_ADD(
            DATE_SUB(
                DATE_ADD('2010-07-06', INTERVAL 1 MONTH),

                INTERVAL DAYOFWEEK(DATE_ADD('2010-07-06', INTERVAL 1 MONTH)) DAY
            ),

            INTERVAL DAYOFWEEK('2010-07-06') DAY
        )