Dates In SQL: You’ll Get What You Ask For

The image I found for “data validation”

I have a dataset that I needed to compare “current month” against the average of the past three months.

In this specific case, I wanted to compare February 2023 vs the average of November 2022 + December 2022 + January 2023.

First I wanted to dynamically find the latest date available in my dataset:

-- Returns 2/28/2023
DECLARE @maxDate AS DATE = (SELECT MAX(the_month) FROM myTable);

Then I wanted to get the date from 3 months prior, and last month:

DECLARE @threeMoAvgStart AS DATE = DATEADD(m, -3, @maxDate);
DECLARE @threeMoAvgEnd AS DATE = DATEADD(m, -1, @maxDate);

Now I could get the average value:

SELECT SUM(the_value) / 3
FROM myTable
WHERE the_month BETWEEN @threeMoAvgStart AND @threeMoAvgEnd

One Other Thing

My dates are stored as the last day of the month: ex. 1/31/2023 and 2/28/2023.

Unexpected Values

Looking at the results, I knew it wasn’t right. To troubleshoot, I printed out the dates that I was calculating:

-- Given that @maxDate = 2/28/2023

-- Returned 11/28/2022
DECLARE @threeMoAvgStart AS DATE = DATEADD(m, -3, @maxDate);

-- Returned 1/28/2023
DECLARE @threeMoAvgEnd AS DATE = DATEADD(m, -1, @maxDate);

Ah hah! While my dates were correctly getting data from Nov 2022 to Jan 2023, it was using January 28 instead of January 31. This is because the first date calculated was February 28, and it was subtracting a month from that.

The Solution

I had to enforce that I’m getting the last day of the month, which can be done with the EOMONTH() function:

DECLARE @threeMoAvgStart AS DATE = EOMONTH( DATEADD(m, -3, @maxDate) );
DECLARE @threeMoAvgEnd AS DATE = EOMONTH( DATEADD(m, -1, @maxDate) );