0

I want to subtract 3 months as so:

DECLARE @ReportingDate date = '6/30/2019'

SELECT  DATEADD(MONTH, -3,@reportingDate)

But what I get returned is '2019-03-30' when the correct end of month should be '2019-03-31'. How do I fix this?

  • What version of SQL Server are you using? If it is 2016 or higher you can use the EOMONTH() function. https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2016 – Isaac Aug 29 '19 at 13:36
  • The answer is correct for three months before an arbitrary date. If you always want then end of the month then a better way is to start with the first of the month. Take off two months and then take off 1 day. That will always force it to the end of the month. – Peter Smith Aug 29 '19 at 13:41

1 Answers1

2

One easy way is:

DECLARE @ReportingDate date = '6/30/2019'

SELECT  CASE
            WHEN @ReportingDate = EOMONTH(@ReportingDate) THEN  EOMONTH(DATEADD(MONTH, -3,@reportingDate))
            ELSE DATEADD(MONTH, -3,@reportingDate)
        END
HoneyBadger
  • 14,750
  • 3
  • 34
  • 48