1

Suppose that I have a date "Jan 31 2011" and I want to add a month to this date.

New date is is okay "Feb 28 2011", when I want to add a month again it gives me "March 28 2011", but I need it to be "March 31 2011".

Oracle has a built-in function for that as Tony Andrews replied to this question,
Add date without exceeding a month

But how to implement this function in PHP? (also MySQL solution is welcome)

edit #1
@zerkms, this is the nearest answer thanks for your help. Here is the second deal

I changed your solution a little bit to solve my real-life problem

SET @BEGINNING_DATE := '2011-01-30'; /* first date for payment */  
SET @NEXT_END_DATE := '2011-02-28'; /* next date for payment (second payment deadline) */

/* now I want to find third payment deadline */

SELECT IF(@BEGINNING_DATE = LAST_DAY(@BEGINNING_DATE), LAST_DAY(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)), DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH))

Your helpful solution gives me "2011-03-28" but I want to "2011-03-30". Any ideas?

edit #2
Here is the solution with @zerkms' help. Thank you!

SET @BEGINNING_DATE := '2011-02-28'; /* first date for payment */
SET @NEXT_END_DATE := '2011-05-31'; /* next date for payment (second payment deadline */

SELECT IF(@BEGINNING_DATE = LAST_DAY(@BEGINNING_DATE), LAST_DAY(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)), CONCAT(YEAR(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)),'-',LPAD(MONTH(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)),2,'0'),'-',DAY(@BEGINNING_DATE)))

edit #3 Here is the exact solution. After dealing with some strange behaviours this is the exact solution

SET @@session.sql_mode = 'ALLOW_INVALID_DATES';
SET @BEGINNING_DATE := '2011-01-29'; /* first date for payment */  
SET @NEXT_END_DATE := '2011-02-28'; /* next date for payment (second payment deadline) */

SELECT IF(@BEGINNING_DATE = LAST_DAY(@BEGINNING_DATE), LAST_DAY(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)), CONCAT(YEAR(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)),'-',MONTH(DATE_ADD(@NEXT_END_DATE, INTERVAL 1 MONTH)),'-',DAY(@BEGINNING_DATE)) + INTERVAL 0 MONTH)
Community
  • 1
  • 1
rasputin
  • 380
  • 5
  • 22
  • In mysql you can do this `DATE_ADD('2006-05-00',INTERVAL 1 MONTH)` to add a month to a specified date – cristian Jan 09 '11 at 09:35
  • Well, `27 feb 2011 + month == 27 march 2011`. `28 feb 2011 + month == 31 march 2011`. Is not it strange? What task are you trying to solve in this way? – zerkms Jan 09 '11 at 09:36
  • if you want the last day of next month look on http://stackoverflow.com/questions/3009896/get-the-first-and-last-date-of-next-month-in-mysql – Haim Evgi Jan 09 '11 at 09:37
  • @Octopus-Paul: `date_add` behaves not the way OP expected. – zerkms Jan 09 '11 at 09:38
  • @zerkms: I need this to calculate the last payment date for customers. – rasputin Jan 09 '11 at 09:47
  • @rasputin: ok. I gave a sample that should satisfy your expectations ;-) – zerkms Jan 09 '11 at 09:48
  • @rasputin: you're comparing to `BEGINNING_DATE` but adding to `NEXT_END_DATE`. Obviously `2011-01-30` is not a last day of 2011 Jan - that is why 1 strict month is added. – zerkms Jan 09 '11 at 10:17
  • @zerkms, yes, of course it is wrong I know. I just wanted to give an example. I think we have to add some code to your query but what is that? I couldn't figure out... – rasputin Jan 09 '11 at 10:22
  • @rasputin: you did not explain what you want to receive. Replace `BEGINNING_DATE` in query with `NEXT_END_DATE` and everything will be fine. – zerkms Jan 09 '11 at 10:24
  • @zerkms, it's no good, it gives me "2011-03-31" but I want "2011-03-30". – rasputin Jan 09 '11 at 10:35

2 Answers2

3
SET @DT := '2011-02-28';

SELECT IF(@DT = LAST_DAY(@DT), LAST_DAY(DATE_ADD(@DT, INTERVAL 1 MONTH)), DATE_ADD(@DT, INTERVAL 1 MONTH));

// returns 2011-03-31

This query takes into account if it is the end of the month or not.

zerkms
  • 249,484
  • 69
  • 436
  • 539
1

MySql has a LAST_DAY function which returns the last day of any given month.

ChristopheD
  • 112,638
  • 29
  • 165
  • 179