Get records for the last month on SQL server

Question

I want to get records for the last month based on the db table [member] "date_created" field.

What is sql for this?

For clarification, last month - from 1/8/2009 to 31/8/2009

If today is 3/1/2010, I will need to get entries from 1/12/2009 to 31/12/2009.

Answer 1

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 need to check the month and year.

Answer 2

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

• They will ignore indexes in the examined column.
• Will (potentially) selects data that is not intended without corrupting your results.

1. Ignored indices:

For the most part, when a column search calls a function called on it (including implicitly, as for CAST ), the optimizer should ignore the indexes in the column and search for each record. Here is a quick example:

We deal with timestamps, and most RDBMSs tend to store this information as an increasing value of some type, usually a long number or a BIGINTEGER milli / nanosecond. Thus, the current time looks / stores as follows:

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

You don't see the meaning of 'Year' ( '2014' ) here, do you? In fact, there is a fairly complicated math to translate back and forth. Therefore, if you call any retrieval / date functions in the found column, the server should do all this math just to find out if you can include it in the results. On small tables, this is not a problem, but as the percentage of selected rows decreases, it becomes a big and big leak. Then in this case you do it a second time to ask about MONTH ... well, you get an image.

2. Unintentional data:

Depending on the specific version of SQL Server and the column data types, using BETWEEN (or similarly included upper bounds: <= ) may result in the selected incorrect data . Essentially, you can end up including data from midnight on the “next” day or excluding some of the “current” daytime entries.

What you should do:

Thus, we need a way that is safe for our data, and we will use indexes (if they are viable). The correct path is:

 WHERE date_created >= @startOfPreviousMonth AND date_created < @startOfCurrentMonth 

Given that there is only one month, @startOfPreviousMonth can easily be replaced with / inferred:

 DATEADD(month, -1, @startOCurrentfMonth) 

If you need to get the beginning of the current month on the server, you can do this through the following:

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

A brief explanation is here. The initial DATEDIFF(...) will get the difference between the beginning of the current era ( 0001-01-01 - AD, CE, whatever), essentially returning a large integer. This is the number of months before the start of the current month. Then we add this number to the beginning of the era that is at the beginning of this month.

So your full script might / should look something 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 

Thus, all operations with dates are performed only once, one value at a time; the optimizer is free to use indexes and no invalid data will be included.

Answer 3

Add the parameters that have been provided so far will not use your indexes at all.

Something like this will do the trick and use the index in 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 

Answer 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 

Update to mrdenny solution, so you get exactly the last month with YYYY-MM-01

Answer 5

One way to do this is to use the DATEPART function:

 select field1, field2, fieldN from TABLE where DATEPART(month, date_created) = 4 and DATEPART(year, date_created) = 2009 

will return all dates in April. For the last month (i.e., Until the current month), you can use GETDATE and DATEADD :

 select field1, field2, fieldN from TABLE where DATEPART(month, date_created) = (DATEPART(month, GETDATE()) - 1) and DATEPART(year, date_created) = DATEPART(year, DATEADD(m, -1, GETDATE())) 

Answer 6

 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())) 

Answer 7

 DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0) SET @EndDate = dateadd(dd, -1, DATEADD(mm, 1, @StartDate)) SELECT * FROM Member WHERE date_created BETWEEN @StartDate AND @EndDate 

and another update to mrdenny solution.
It also gives the last day of the previous month.

Answer 8

 declare @PrevMonth as nvarchar(256) SELECT @PrevMonth = DateName( month,DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)) + '-' + substring(DateName( Year, getDate() ) ,3,4) 

Answer 9

Last month, consider until the last day of the month. 01/31/2016 here the last day of the month will be January 31, which is not like the last 30 days.

 SELECT CONVERT(DATE, DATEADD(DAY,-DAY(GETDATE()),GETDATE())) 

Answer 10

In the Sql server for the last month:

 select * from tablename where order_date > DateAdd(WEEK, -1, GETDATE()+1) and order_date<=GETDATE() 

Answer 11

 WHERE date_created >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND date_created < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) 

Answer 12

 DECLARE @curDate INT = datepart( Month,GETDATE()) IF (@curDate = 1) BEGIN select * from Featured_Deal where datepart( Month,Created_Date)=12 AND datepart(Year,Created_Date) = (datepart(Year,GETDATE())-1) END ELSE BEGIN select * from Featured_Deal where datepart( Month,Created_Date)=(datepart( Month,GETDATE())-1) AND datepart(Year,Created_Date) = datepart(Year,GETDATE()) END 

Answer 13

 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) set @StartDate = DATEADD(dd, 1 , @StartDate) 

Answer 14

I am from Oracle env and I would do this in Oracle:

 select * from table where trunc(somedatefield, 'MONTH') = trunc(sysdate -INTERVAL '0-1' YEAR TO MONTH, 'MONTH') 

Idea: I run a scheduled report of the previous month (from the first day to the last day of the month, not the window). This may be an unscrupulous index, but Oracle has fast date processing anyway. Is there a simple and short way in MS SQL? The answer comparing the year and month separately seems silly to Oracle people.

Answer 15

You can get records of the last month with this request

SELECT * FROM dbo.member d WHERE CONVERT (DATE, date_created, 101)> = CONVERT (DATE, DATEADD (m, dateiff (m, 0, current_timestamp) -1, 0)) and CONVERT (DATE, date_created, 101) < CONVERT (DATE, DATEADD (m, dateiff (m, 0, current_timestamp) -1, 0), 101)