Get the records of last month in SQL server

sql

performance

best-practices

date-functions

byAnton Shumikhin·Sep 17, 2024

Here's a little SQL magic to fetch last month's records. We use EOMONTH for nailing down the start and end dates:

SELECT *
FROM TableName
WHERE DateColumn >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)))
AND DateColumn < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1));

Note the use of >= and <—inclusive start, exclusive end. The perfect fit for getting the exact range of last month.

Performance through optimized querying

To boost efficiency, do the date calculations outside your WHERE clause. With this, we can handle indexes like a rockstar and greatly enhance performance:

-- "Slayin' redundancy, one query at a time!" - Anonymous SQL developer
DECLARE @StartDate DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);
DECLARE @EndDate DATE = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

SELECT *
FROM YourTable
WHERE DateCreated >= @StartDate AND DateCreated < @EndDate;

Here's a pro-tip: Using variables for date calculations wards off repeated evaluation horrors, speeding up your SQL.

Leverage dynamic range calculation

Ever played poker with the calendar? Leap years or months with varied lengths can bluff you. Here's how to keep an ace up your sleeve:

-- "Time you enjoyed wasting, wasn't wasted!" - John Lennon
-- Well, not in case of my SQL server
DECLARE @StartOfCurrentMonth DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
DECLARE @EndOfPreviousMonth DATE = DATEADD(DAY, -1, @StartOfCurrentMonth);

SELECT *
FROM YourTable
WHERE DateCreated >= DATEADD(MONTH, -1, @StartOfCurrentMonth)
AND DateCreated < @StartOfCurrentMonth;

This snippet disguises as a quantum supercomputer to handle leap years and any month lengths. Be assured, this won't put a dent in the time-space continuum!

Careful bounds determination

You might be tempted to use BETWEEN, but it's a double-edged sword. Its inclusive nature might return more records than you bargained for. An explicit comparison (>= and <) is your trusty knight in shining armor.

Utilizing EOMONTH for date slicing

EOMONTH is DYI home kit for finding exact month-end dates. It comes handy when you need to get last day of the previous month:

-- "All good things come to an end." (even months!)
-- Use this logic to eke out the last bit of data from previous month.
DECLARE @LastDayOfPreviousMonth DATE = EOMONTH(GETDATE(), -1);
DECLARE @FirstDayOfPreviousMonth DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(), -2));

SELECT *
FROM EventTable
WHERE EventDate >= @FirstDayOfPreviousMonth AND EventDate <= @LastDayOfPreviousMonth;

With this snippet, you can pinpoint start and end dates without having to count the days manually.

Getting most out of indexes

Indexes expedite your queries, especially when dealing with vast datasets. Key strategy: avoid functions around your date column in the WHERE clause. You want those indexes to shine, not bench. All strategies elucidated utilize variables and date functions optimally to keep indexes busy.

Handling edge cases with style

Fetching last month records isn't always a walk in the park. What if it's January? You have to give date_created a double-check to ensure that it's considering the correct year:

-- "I know I was last year... but I will be next year too!" - December
SELECT *
FROM FinancialRecords
WHERE YEAR(DateTransaction) = YEAR(DATEADD(MONTH, -1, GETDATE()))
AND MONTH(DateTransaction) = MONTH(DATEADD(MONTH, -1, GETDATE()));