Problem
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:
DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
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)
SELECT *
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
SELECT *
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).
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate = dateadd(dd, datepart(dd, getdate())*-1, @StartDate)
SET @EndDate = dateadd(mm, 1, @StartDate)
SELECT *
FROM Member
WHERE date_created BETWEEN @StartDate AND @EndDate
Answered by mrdenny
Solution #4
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
SET @EndDate = DATEADD(mm, 1, @StartDate)
SELECT *
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.
SELECT CONVERT(DATE, DATEADD(DAY,-DAY(GETDATE()),GETDATE()))
Answered by M2012
Post is based on https://stackoverflow.com/questions/1424999/get-the-records-of-last-month-in-sql-server