Coder Perfect

In SQL server, get the records from the previous month.


I’d want to get the records from the previous month using the [member] field “date created” from my database table.

What sql should I use to accomplish this?

To be clear, last month was from 1/8/2009 to 31/8/2009.

If today is 3/1/2010, I’ll need the records from December 1, 2009 to December 31, 2009.

Asked by Billy

Solution #1

All of the current (functioning) solutions have one of two issues:

1. Ignored Indices:

When a function is executed on a column being searched (including implicitly, as in CAST), the optimizer must ignore indices on the column and search through every record. As an example, consider the following:

We’re working with timestamps, which are often stored as a rising number of some type, commonly a long or BIGINTEGER count of milli-/nanoseconds. As a result, the current time appears/is kept as follows:

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

Do you notice the ‘Year’ value (‘2014’) is missing? In fact, there’s quite a bit of difficult arithmetic involved in the back-and-forth translation. If you use any of the extraction/date part operations on the searched column, the server needs to do all of that arithmetic simply to see if it can be included in the results. This isn’t an issue for small tables, but as the percentage of rows picked falls, it becomes a more and bigger burden. Then, in this scenario, you’re doing it a second time because you’re curious about MONTH… well, you get the idea.

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. In other words, you could wind up including data from the “next” day’s midnight or excluding a portion of the “current” day’s information.

What you should do instead:

So we’ll use indices to find a safe way to store our data (if viable). The correct procedure is as follows:

WHERE date_created >= @startOfPreviousMonth AND date_created < @startOfCurrentMonth

Because there is only one month, @startOfPreviousMonth can easily be replaced with/derived from:

DATEADD(month, -1, @startOCurrentfMonth)

If you need to calculate the start-of-current-month in the server, use the following syntax:


Here’s a quick rundown of what’s going on. The original DATEDIFF(…) returns the difference between the start of the current era (0001-01-01 – AD, CE, whatever) and the start of the previous era (0001-01-01 – AD, CE, whatever), effectively returning a huge integer. This is the number of months till the current month begins. Then we multiply this number by the commencement of the period, which is the first day of the month.

As a result, your complete script could/should look like this:

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

FROM Member
WHERE date_created >= DATEADD(month, -1, @startOfCurrentMonth) -- this was originally    misspelled
      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.

Answered by Clockwork-Muse

Solution #2

FROM Member
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

You must verify the month and year.

Answered by Dave Barker

Solution #3

The alternatives that have been offered thus far will not use your indexes in any way.

Something like this will do the trick, and make use of an index on the table (if one exists).

SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate = dateadd(dd, datepart(dd, getdate())*-1, @StartDate)
SET @EndDate = dateadd(mm, 1, @StartDate)

FROM Member
WHERE date_created BETWEEN @StartDate AND @EndDate

Answered by mrdenny

Solution #4

SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
SET @EndDate = DATEADD(mm, 1, @StartDate)

FROM Member
WHERE date_created BETWEEN @StartDate AND @EndDate

Upgrade to mrdenny’s solution, which gives you the actual month from YYYY-MM-01.

Answered by Rokas

Solution #5

Last month is calculated from the first to the last day of the month. The end day of the month here is 31 January, which is not the same as the previous 30 days.


Answered by M2012

Post is based on