How to get First and Last date of the Previous Month or Week

 As a report/database developer I came across the need to calculate the Start and End of the previous month, as well as of the previous week quite a few times. This was especially useful when the report or query was scheduled to run at a given day during the month or week, and the scheduling server did not have a StartofPrevious/EndofPrevious Month or Week function.

Here is the code I developed and a quick explanation of the logic behind it.


DECLARE @d smalldatetime;
SET @d = CAST('7/11/2009' as smalldatetime);
SELECT 

@d as OriginalDate,
DAY(@d) DayofTheMonth,
CAST(DATEADD(month,-1,dateadd(day,-DATEPART(day,@d)+1, @d)) as smalldatetime) as StartofPreviousMonth,
CAST(DATEADD(day,-DATEPART(day,@d), @d) as smalldatetime) as EndofPreviousMonth,
DATEPART(weekday,@d) as DayofTheWeek,
CAST(DATEADD(week,-1,DATEADD(day,-(DATEPART(weekday,@d))+2, @d)) as smalldatetime) as MondayofPreviousWeek,
CAST(DATEADD(day,-(DATEPART(weekday,@d))+1, @d) as smalldatetime) as SundayofPreviousWeek;

    To get the StartofPreviousMonth, I subtracted the day of the month + 1 (that would return the first day of the current month) and then subtracted a month (to get the first day of the previous month).
7/11/09 – 11 days + 1 day – 1 month = 6/01/09

    To get the EndofPreviousMonth, I subtracted the day of the month (in this case 11) from the date variable.
7/11/09 – 11 days = 6/30/09

    To get the MondayofPreviousWeek, I subtract the weekday and add 2 days to the date variable (that would give us the Monday of this week), then subtracted a full week to get the Monday of the previous week.
7/11/09 – 7 days + 2 days – 1 week = 6/29/09

    To get the SundayofPreviousWeek, I subtract the weekday then add 1 day.
7/11/09 – 7 days + 1 days= 7/05/09

*Note Keep in mind that when you use DATEPART(Weekday,@d) the integer returned is 1 for Sunday, 2 for Monday and so on.

Posted in: How to get First and Last date of the Previous Month or Week