Get start and end of previous month [duplicate]

Question

Using this syntax the results are previous week on run date, I want previous month on run date

SELECT @wrkBeginDate = DATEADD(dd,-(DATEPART(dw,GetDate())+6), GetDate()),
       @wrkEndDate   = DATEADD(dd,-(DATEPART(dw,GetDate())),   GetDate()) 

SELECT @wrkBeginDate = DATEADD(mm,-(DATEPART(dw,GetDate())+6), GetDate()),
       @wrkEndDate   = DATEADD(mm,-(DATEPART(dw,GetDate())),   GetDate()) 

Answer 1

You can use EOMONTH() to find the beginning and end of any month:

-- the last day of last month is EOMONTH(getdate(), -1):
DECLARE @EndOfLastMonth date = EOMONTH(getdate(), -1);

-- the last day of two months ago is EOMONTH(getdate(), -2)
-- then add a day:
DECLARE @StartOfLastMonth date = DATEADD(DAY, 1, EOMONTH(getdate(), -2));

SELECT @StartOfLastMonth, @EndOfLastMonth;

---

Also see Simplify Date Period Calculations for a bunch of others. At the time I preferred DATEFROMPARTS, but now I've embraced EOMONTH. In SQL Server 2022, DATETRUNC and DATE_BUCKET are options as well.

---

As an aside, note that your current expression for week is unstable. Try SET DATEFIRST 1; - a user with this setting (maybe unknowingly) will get Monday - Sunday instead of Sunday - Saturday. If that's expected/desired, cool. If not, you need to account for that:

DECLARE @StartOfLastWeek date = DATEADD(DAY, 
   -(DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7, GETDATE());

DECLARE @EndOfLastWeek   date = DATEADD(DAY, 6, @StartOfLastWeek);

This will get Sunday to Saturday regardless of current SET DATEFIRST setting. It may be tempting to use DATENAME as part of the calculation, but now you're chaining yourself to SET LANGUAGE settings.

Answer 2

To simplify this, we can reduce both requirements to exactly one operation from the start of the current month:

The first day of the prior month is exactly one month before the first day of the current month.

DATEADD(month, -1, {StartOfMonth})

The last day of the prior month is exactly one day before the first day of the current month.

DATEADD(day, -1, {StartOfMonth})

---

So how do we find the first day of the current month? There are threefour valid ways:

1. The old DateAdd/DateDiff from the beginning of time trick for the month interval

   DATEADD(month, DATEDIFF(month, 0, current_timestamp), 0)
   

2. Use DateFromParts() and Year()/Month() for day 1

   DATEFROMPARTS(year(current_timestamp), month(current_timestamp), 1)
   

3. Use EOMonth() (one day after one month before the end of the current month)

    DATEADD(month, -1, dateadd(day, 1, EOMonth(current_timestamp)))
   

   Which I just learned today can be simplified like this (thanks again Aaron):

    DATEADD(day, 1, EOMonth(current_timestamp, -1))
   

4. DateTrunc(), the new hotness in SQL Server 2022

    DATETRUNC(month, current_timestamp)
   

I'm comfortable with any of those. While certainly one is faster than the others, they're all reasonably performant and they all use reliable date math; they do NOT construct the value from a string, which is something you should avoid.

If you have SQL Server 2022, I'd probably pick the last one (DATETRUNC()), because it's easily the shortest and simplest to understand. But otherwise take whichever of these you want and plug them in for the StartOfMonth expressions above.

---

Finally, I'll add that if you intend to use these as boundaries for a range query, you're typically better off using a half-open range, where the end boundary is an exclusive test for the day after your range closes; that is, the first day of the current month.

So instead of eventually doing this:

WHERE MyDateColumn BETWEEN @WorkMonthBeginDate AND @WorkMonthEndDate

You should be trying for something like this:

WHERE   MyDateColumn >= @WorkMonthBeginDate 
    AND MyDateColumn <  @NextMonthBeginDate

This way, in the case where the column may change to start also storing time values, the range still covers the entire month, and doesn't leave out values after the initial tick of the final day. It doesn't always matter, but this also helps you tend to naturally end up in a better place regarding index use.