Filter Table by a Historical 'Last Day of the Month'

Question

I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month. For example:

Table:

DwDate            Balance
20181231           $10
20190131           $80   
20190228           $75
20190331           $50

99% of the time I just need the MAX(DwDate), which would be:

@MaxDate = (SELECT MAX(DwDate) FROM dbo.TABLE)

However, occasionally I need a formula that would allow me to go back 1 or 2 months:

@MaxDate - 2 Months

Answer 1

To subract a month from a date you can use dateadd().

For example

SET @MaxDate = dateadd(month, -1, @MaxDate);

would subtract one month from your @MaxDate. That would keep the day of the month though. (Unless this day doesn't exists for the month, then it's the last day of the month, so e.g. the 31th day might become the 30th.)

To get the date of the last day of a month a date is in eomonth() can be used.

SET @MaxDate = eomonth(@MaxDate);

would set @MaxDate to the last day of the month @MaxDate was previously in.

eomonth() optionally takes a second argument, which is an integer to add to the month. So to set @MaxDate to the last day of the previous month of @MaxDate you might use

SET @MaxDate = eomonth(@MaxDate, -1);

Answer 2

I upvoted @StickyBits answer, but wanted to suggest the usage of a Date Dimension table (if you don't already have one) as another option. In addition to providing you the ability to determine the last day of any month, there is a wealth of other information that could also be used once you find a particular row in the Date Dimension.

Check out Aaron Bertrand's post regarding Creating a date dimension or calendar table in SQL Server. Aaron's example Date Dimension starts off with a basic set of columns and then he shows you how to add other attributes.

CREATE TABLE dbo.DateDimension
(
  --DateKey           INT         NOT NULL PRIMARY KEY,
  [Date]              DATE        NOT NULL,
  [Day]               TINYINT     NOT NULL,
  DaySuffix           CHAR(2)     NOT NULL,
  [Weekday]           TINYINT     NOT NULL,
  WeekDayName         VARCHAR(10) NOT NULL,
  IsWeekend           BIT         NOT NULL,
  IsHoliday           BIT         NOT NULL,
  HolidayText         VARCHAR(64) SPARSE,
  DOWInMonth          TINYINT     NOT NULL,
  [DayOfYear]         SMALLINT    NOT NULL,
  WeekOfMonth         TINYINT     NOT NULL,
  WeekOfYear          TINYINT     NOT NULL,
  ISOWeekOfYear       TINYINT     NOT NULL,
  [Month]             TINYINT     NOT NULL,
  [MonthName]         VARCHAR(10) NOT NULL,
  [Quarter]           TINYINT     NOT NULL,
  QuarterName         VARCHAR(6)  NOT NULL,
  [Year]              INT         NOT NULL,
  MMYYYY              CHAR(6)     NOT NULL,
  MonthYear           CHAR(7)     NOT NULL,
  FirstDayOfMonth     DATE        NOT NULL,
  LastDayOfMonth      DATE        NOT NULL,
  FirstDayOfQuarter   DATE        NOT NULL,
  LastDayOfQuarter    DATE        NOT NULL,
  FirstDayOfYear      DATE        NOT NULL,
  LastDayOfYear       DATE        NOT NULL,
  FirstDayOfNextMonth DATE        NOT NULL,
  FirstDayOfNextYear  DATE        NOT NULL
);
GO

Using DateAdd to maneuver forward and backwards, you can find a particular row in the Date Dimension table and have access to many additional attributes about a particular date.

SELECT *
FROM dbo.DateDimension
WHERE [Date] = dateadd(month, - 2, convert(DATE, getdate()));