Filter Table by a Historical 'Last Day of the Month' 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
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);