Get the records of last month in SQL server

Question

I want to get the records of last month based on my database table [member] field date_created.

How can I use SQL to do this?

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

If today is 3/1/2010, I'll need to get the records of 1/12/2009 to 31/12/2009.

Answer 1

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.

Answer 2

Here is what I did so I could put it in a view:

ALTER view [dbo].[MyView] as

with justdate as (
select getdate() as rightnow
)
, inputs as (
select dateadd(day, 1, EOMONTH(jd.rightnow, -2)) as FirstOfLastMonth
      ,dateadd(day, 1, EOMONTH(jd.rightnow, -1)) as FirstOfThisMonth
  from justdate jd
)
SELECT TOP 10000
       [SomeColumn]
      ,[CreatedTime]
  from inputs i
  join [dbo].[SomeTable]
    on createdtime >= i.FirstOfLastMonth
   and createdtime < i.FirstOfThisMonth
 order by createdtime
;

Note that I intentionally ran getdate() once.

Answer 3

SELECT * FROM Member WHERE month(date_created) = month(NOW() - INTERVAL 1 MONTH);

Answer 4

I don't think the accepted solution is very index friendly I use the following lines instead

select * from dbtable where the_date >= convert(varchar(10),DATEADD(m, -1, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120) and the_date <= dateadd(ms, -3, convert(varchar(10),DATEADD(m, 0, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120));

Or simply (this is the best).

select * from dbtable where the_date >= convert(varchar(10),DATEADD(m, -1, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120) and the_date < SELECT convert(varchar(10),DATEADD(m, -1, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120);

Some help

-- Get the first of last month
SELECT convert(varchar(10),DATEADD(m, -1, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120);
-- Get the first of current month
SELECT convert(varchar(10),DATEADD(m, -1, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120);
--Get the last of last month except the last 3milli seconds. (3miliseconds being used as SQL express otherwise round it up to the full second (SERIUSLY MS)
SELECT dateadd(ms, -3, convert(varchar(10),DATEADD(m, 0, dateadd(d, - datepart(dd, GETDATE())+1, GETDATE())),120));

Answer 5

If you are looking for previous month data:

date(date_created)>=date_sub(date_format(curdate(),"%Y-%m-01"),interval 1 month) and 
date(date_created)<=date_sub(date_format(curdate(),'%Y-%m-01'),interval 1 day)

This will also work when the year changes. It will also work on MySQL.

Answer 6

A simple query which works for me is:

select * from table where DATEADD(month, 1,DATEFIELD) >= getdate()

Answer 7

If you are looking for last month so try this,

SELECT
FROM  #emp 
WHERE DATEDIFF(MONTH,CREATEDDATE,GETDATE()) = 1

If you are looking for last month so try this,

SELECT
FROM #emp
WHERE DATEDIFF(day,CREATEDDATE,GETDATE()) between 1 and 30

Answer 8

The way I fixed similar issue was by adding Month to my SELECT portion

Month DATEADD(day,Created_Date,'1971/12/31') As Month

and than I added WHERE statement

Month DATEADD(day,Created_Date,'1971/12/31') = month(getdate())-1

Answer 9

You can get the last month records with this query

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

Answer 10

SQL query to get record of the present month only

SELECT * FROM CUSTOMER
WHERE MONTH(DATE) = MONTH(CURRENT_TIMESTAMP) AND YEAR(DATE) = YEAR(CURRENT_TIMESTAMP);

Answer 11

I'm from Oracle env and I would do it like this in Oracle:

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

Idea: I'm running a scheduled report of previous month (from day 1 to the last day of the month, not windowed). This could be index unfriendly, but Oracle has fast date handling anyways. Is there a similar simple and short way in MS SQL? The answer comparing year and month separately seems silly to Oracle folks.

Answer 12

Last month consider as till last day of the month. 31/01/2016 here last day of the month would be 31 Jan. which is not similar to last 30 days.

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

Answer 13

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

Answer 14

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 15

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 16

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 17

Add the options which have been provided so far won't use your indexes at all.

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

Answer 18

declare @PrevMonth as nvarchar(256)

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

Answer 19

In Sql server for last one month:

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

Answer 20

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 upgrade to mrdenny's solution.
It gives the exact last day of the previous month as well.

Answer 21

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

An upgrade to mrdenny's solution, this way you get exactly last month from YYYY-MM-01

Answer 22

One way to do it is using 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 last month (ie, previous to current month) you can use GETDATE and DATEADD as well:

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 23

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