First and Last of Month Calculations This is probably a rudimentary question for most, but for some reason I'm not getting it right.  I currently use two specific SET formulas for "1st day of last month" and "Last day of last month"

1st day of last month (11/1, November 1st):

SET @strFirstofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(MM, -1, @dtDate)), 0), 112))


Last day of last month (11/30, November 30th):

SET @strLastofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @dtDate))), -1), 112))


I'm now needing a formula for the 1st day of the month before last, and the last day of the month before last

Example.  If today is 12/6 (December 6), I need a formula for 10/1 (October 1st) and 10/31 (October 31st).
DECLARE @dtDate date = GETDATE();

-- month before last:
DECLARE @TwoMonthsAgo date = DATEADD(MONTH,-2,@dtDate);

-- first and last of that month:
SELECT
  FirstOfMonth = DATEFROMPARTS(YEAR(@TwoMonthsAgo),MONTH(@TwoMonthsAgo),1),
  LastOfMonth  = EOMONTH(@TwoMonthsAgo);


But then what are you going to do with that? You can't say:

SELECT cols FROM dbo.table 
WHERE datecol BETWEEN @FirstOfMonth AND @LastOfMonth;


Why? Because if datecol also has time, you miss everything after midnight on the last day of the month.

Much better to just find the beginning of the current period, and from there it is trivial to find the beginning of the next period, and use an open-ended range instead of close-ended BETWEEN:

WHERE datecol &gt;= @FirstOfMonth
  AND datecol &lt;  DATEADD(MONTH, 1, @FirstOfMonth);


Incidentally, I just gave a whole presentation on this topic this morning.

Some tips for further reading:


Don't use variable types like varchar without specifying the length
Don't use shorthand like m - if you mean month, type MONTH
Don't use BETWEEN for date range queries