CODEWITHSUNDEEP
phpmysqldatetimedate
rasputin
rasputin

Reputation: 380

Adding months to a date with keeping the last day of given month

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)

Upvotes: 1

Views: 903

Answers (2)

zerkms
zerkms

Reputation: 254926

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.

Upvotes: 3

ChristopheD
ChristopheD

Reputation: 116157

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

Upvotes: 1

Related Questions