This is an age-old problem for calendars where some months have different numbers of days than other months. I don't know of any truly satisfying answer to this problem, but I'd like to hear if anyone ever finds one!
The problem lies in the transition from a long month (such as January) to a short month (such as February). When you add a month to any day after January 28 (or 29 on a leap year), just where does that leave you? The commonly accepted answer is the last day of the next month.
The transition from a short month (like February) to a long month (like March) isn't such a problem. A month after February 28th is always going to be March 28th, at least for most people's use.
At least this calendar has a predictable number of days per month. There are some calendars where you can't truly know when next month will start (there are calendars that depend on a human observer to declare the first of every month, so it can move by one day on any given month)!
So you are treating the last day of the month as a "special case". IE: if you were adding a month to February 27, would you want the result to be March 27 or March 30 (the second-to-last day of March). If so, then the problem lies with the fact that YOU are not being consistent in your handling of dates, and is not an issue with the DATEADD function. You will need to write a custom function if you want non-standard behavior.
If it's not practically useful, then it's practically useless.
The following is the code I tried
CREATE TABLE #TEMP(PDATE DATETIME)
SELECT @CNT = 1
SELECT @PERIODM = 60
SELECT @PDATE = '01/31/2005' -- if this value is '01/31/2005' then in second row it will be '02/28/2005' and in third row I want '03/31/2005'
WHILE @CNT <= @PERIODM
INSERT INTO #TEMP
SELECT @CNT = @CNT + 1
IF MONTH(@PDATE) = MONTH(DATEADD(DAY, 1, @PDATE))
SELECT @PDATE = DATEADD(MONTH, 1, @PDATE)
SELECT @PDATE = ??????????????
-- DATEADD(MONTH, 1, DATEADD(DAY, -1, @PDATE))
SELECT * FROM #TEMP ORDER BY 1
If the first row contains 01/30/2005, what should the next five rows be? There are some really complex problems to deal with here, in that there isn't any 02/30/2005, but there is a 03/30/2005 and a 04/30/2005 which happens to be the last day of that month!
This is a really, really slippery slope... Think carefully about what you want, then if you can give me an example that goes from January 30 through May, I'll see if I know enough to write some code to help you get it.
-- -- First day of last month
-- -- Last Day of last Month