1. Registered User
Join Date
Jan 2005
Posts
8

Hi,
I'm getting problem while using DATEADD Function.

When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.

2. Registered User
Join Date
Feb 2004
Posts
492
What happens if you'd add a month and subtract one day?

3. Registered User
Join Date
Jan 2005
Posts
8

How do I know if the date that supplied is the last day of the month ?

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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)!

-PatP

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by prajesh
How do I know if the date that supplied is the last day of the month ?
Code:
```DECLARE @myDate DATETIME
SELECT @myDate = GetDate()

IF Month(@myDate) = Month(DateAdd(day, 1, @myDate))
THEN PRINT "mid-month"
ELSE PRINT "Today is a month-end"```
-PatP

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

7. Registered User
Join Date
Jan 2005
Posts
8
Thanks for suggestion Pat. I'll try that on my stored procedure.

8. Registered User
Join Date
Jan 2005
Posts
8
Hi PatP,
The following is the code I tried
DECLARE
@CNT INT,
@PERIODM INTEGER,
@PDATE DATETIME

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
BEGIN
INSERT INTO #TEMP
VALUES(@PDATE)

SELECT @CNT = @CNT + 1
IF MONTH(@PDATE) = MONTH(DATEADD(DAY, 1, @PDATE))
SELECT @PDATE = DATEADD(MONTH, 1, @PDATE)
ELSE
SELECT @PDATE = ??????????????
END
SELECT * FROM #TEMP ORDER BY 1

DROP TABLE #TEMP

How do I do, please suggest..

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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.

-PatP

10. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
Is the original problem to get the last day of the next month?

I'd decompose the problem to determine the first day of two month later, and subtract 1 day, so

12/31/2004 --> 02/01/2005 --> 01/31/2005

If this is, want you want to achieve, the code can be done easily.

11. Registered User
Join Date
Jan 2005
Posts
2

## try this..

Declare @date nvarchar(20)
set @date = '2/5/2004'

-- -- First day of last month
--
-- -- Last Day of last Month