Get the records of last month in SQL server

by

All the existing (working) answers have one of two problems:

  1. They will ignore indices on the column being searched
  2. The will (potentially) select data that is not intended, silently corrupting your results.

1. Ignored Indices:

For the most part, when a column being searched has a function called on it (including implicitly, like for CAST), the optimizer must ignore indices on the column and search through every record. Here’s a quick example:

We’re dealing with timestamps, and most RDBMSs tend to store this information as an increasing value of some sort, usually a long or BIGINTEGER count of milli-/nanoseconds. The current time thus looks/is stored like this:

1402401635000000  -- 2014-06-10 12:00:35.000000 GMT

You don’t see the ‘Year’ value ('2014') in there, do you? In fact, there’s a fair bit of complicated math to translate back and forth. So if you call any of the extraction/date part functions on the searched column, the server has to perform all that math just to figure out if you can include it in the results. On small tables this isn’t an issue, but as the percentage of rows selected decreases this becomes a larger and larger drain. Then in this case, you’re doing it a second time for asking about MONTH… well, you get the picture.

2. Unintended data:

Depending on the particular version of SQL Server, and column datatypes, using BETWEEN (or similar inclusive upper-bound ranges: <=) can result in the wrong data being selected. Essentially, you potentially end up including data from midnight of the “next” day, or excluding some portion of the “current” day’s records.

What you should be doing:

So we need a way that’s safe for our data, and will use indices (if viable). The correct way is then of the form:

WHERE date_created >= @startOfPreviousMonth AND date_created < @startOfCurrentMonth

Given that there’s only one month, @startOfPreviousMonth can be easily substituted for/derived by:

DATEADD(month, -1, @startOfCurrentMonth)

If you need to derive the start-of-current-month in the server, you can do it via the following:

DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

A quick word of explanation here. The initial DATEDIFF(...) will get the difference between the start of the current era (0001-01-01 – AD, CE, whatever), essentially returning a large integer. This is the count of months to the start of the current month. We then add this number to the start of the era, which is at the start of the given month.

So your full script could/should look similar to the following:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

SELECT *
FROM Member
WHERE date_created >= DATEADD(month, -1, @startOfCurrentMonth)
      AND date_created < @startOfCurrentMonth

All date operations are thus only performed once, on one value; the optimizer is free to use indices, and no incorrect data will be included.

Leave a Comment