获取上个月在 SQL Server 中的记录

Get the records of last month in SQL server

提问by Billy

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

我想根据我的数据库表 [member] 字段“date_created”获取上个月的记录。

What's the sql to do this?

执行此操作的 sql 是什么？

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

澄清，上个月 - 1/8/2009 至 31/8/2009

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

如果今天是 3/1/2010，我需要获取 1/12/2009 到 31/12/2009 的记录。

回答by Dave Barker

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.

您需要检查月份和年份。

回答by Clockwork-Muse

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. 他们将忽略正在搜索的列上的索引
2. 将（可能）选择非预期的数据，默默地破坏您的结果。

1. Ignored Indices:

1. 忽略指数：

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:

大多数情况下，当被搜索的列有一个调用它的函数时（包括隐式，如 for CAST），优化器必须忽略列上的索引并搜索每条记录。这是一个快速示例：

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

我们正在处理时间戳，并且大多数 RDBMS 倾向于将此信息存储为某种递增的值，通常是毫秒/纳秒long或BIGINTEGER计数。当前时间因此看起来/存储如下：

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.

您'2014'在那里没有看到“年份”值 ( ) ，是吗？事实上，有相当多的复杂数学需要来回转换。因此，如果您在搜索列上调用任何提取/日期部分函数，​​服务器必须执行所有数学运算才能确定您是否可以将其包含在结果中。在小表上，这不是问题，但随着所选行的百分比减少，这会变得越来越大。那么在这种情况下，你第二次这样做是为了询问MONTH......好吧，你明白了。

2. Unintended data:

2. 意外数据：

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.

取决于在SQL Server的特定版本，并且列数据类型，使用BETWEEN（或类似的含上限范围：<=）可能导致错误的数据被选择。从本质上讲，您最终可能会包含“下一天”午夜的数据，或者排除“当前”天记录的某些部分。

What you shouldbe 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, @startOfPreviousMonthcan be easily substituted for/derived by:

鉴于只有一个月，@startOfPreviousMonth可以很容易地替换/导出：

DATEADD(month, -1, @startOCurrentfMonth)

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 currentmonth. We then add this number to the start of the era, which is at the start of the given month.

在这里快速解释一下。初始DATEDIFF(...)将获得当前时代开始之间的差异（0001-01-01- AD，CE，等等），本质上返回一个大整数。这是到当前月份开始的月份数。然后我们将此数字添加到时代的开始，即给定月份的开始。

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

因此，所有日期操作仅对一个值执行一次；优化器可以自由使用索引，并且不会包含不正确的数据。

回答by mrdenny

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

回答by Rokas

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

升级到 mrdenny 的解决方案，这样您就可以从 YYYY-MM-01 获得上个月的准确信息

回答by M2012

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.

上个月视为到本月的最后一天。2016 年 1 月 31 日这里的最后一天是 1 月 31 日，这与过去 30 天不同。

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

回答by Vinko Vrsalovic

One way to do it is using the DATEPARTfunction:

一种方法是使用DATEPART函数：

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 GETDATEand DATEADDas well:

将返回四月的所有日期。对于上个月（即前一个月），您也可以使用GETDATE和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()))

回答by sheetal

declare @PrevMonth as nvarchar(256)

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

回答by Mohammad Abraq

SQL query to get record of the present month only

SQL查询只获取当月的记录

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

回答by Kostya

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

回答by DmitryK

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