I want to get the records of last month based on my db table [member] field "date_created".
我想根据我的db表[成员]字段“date_created”获取上个月的记录。
What's the sql to do this?
sql要做什么呢?
For clarification, last month - 1/8/2009 to 31/8/2009
详情请参阅上月- 2009年1月8日至2009年8月31日
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的记录。
17 个解决方案
#1
76
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.
你需要查看月份和年份。
#2
58
All the existing (working) answers have one of two problems:
所有现有的(工作的)答案都有两个问题:
- They will ignore indices on the column being searched
- 它们将忽略正在搜索的列上的索引
- The will (potentially) select data that is not intended, silently corrupting your results.
- 将(潜在地)选择不打算的数据，悄悄破坏您的结果。
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:
在大多数情况下，当被搜索的列上有一个调用它的函数(包括隐式的，如CAST)时，优化器必须忽略列上的索引，并在每个记录中搜索。这里有一个简单的例子:
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:
我们正在处理时间戳，大多数rdbms都倾向于将这些信息存储为某种递增值，通常是毫秒/纳秒的长或大整数计数。当前的时间是这样存储的:
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”)，是吗?事实上，有相当多复杂的数学数据需要来回转换。因此，如果您调用搜索列上的任何提取/日期部分函数，服务器必须执行所有这些运算，以确定是否可以将其包含到结果中。在小表上，这不是问题，但随着所选行的百分比减少，这将变得越来越大。在这种情况下，你是第二次问大约一个月……好吧，你懂的。
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 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:
如果只有一个月，@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 current month. 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.
因此，所有的日期操作只在一个值上执行一次;优化器可以*使用索引，并且不会包含不正确的数据。
#3
12
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
#4
8
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的解决方案，这样您就可以从yyyyy - mm -01上得到上个月的结果
#5
2
One way to do it is using the DATEPART function:
一种方法是使用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 GETDATE and DATEADD as well:
将在4月份返回所有日期。对于上个月(即前一个月)，您还可以使用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()))
#6
2
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()))
#7
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()))
#8
1
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.
这是对mrdenny解决方案的又一次升级。它给出了前一个月的最后一天。
#9
1
declare @PrevMonth as nvarchar(256)
SELECT @PrevMonth = DateName( month,DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)) +
'-' + substring(DateName( Year, getDate() ) ,3,4)
#10
1
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);
#11
0
In Sql server for last one month:
在Sql server上一个月:
select * from tablename
where order_date > DateAdd(WEEK, -1, GETDATE()+1) and order_date<=GETDATE()
#12
0
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
#13
0
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)
#14
0
WHERE
date_created >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND date_created < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
#15
0
I'm from Oracle env and I would do it like this in Oracle:
我来自Oracle env，我会在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.
想法:我正在运行上个月的计划报告(从第一天到最后一天，而不是窗口)。这可能是索引不友好，但是Oracle有快速数据处理。在MS SQL中有类似的简单和简短的方法吗?对甲骨文公司的人来说，分别比较年份和月份的答案似乎很愚蠢。
#16
0
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)
#17
0
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
然后加上WHERE语句
Month DATEADD(day,Created_Date,'1971/12/31') = month(getdate())-1
#1
76
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.
你需要查看月份和年份。
#2
58
All the existing (working) answers have one of two problems:
所有现有的(工作的)答案都有两个问题:
- They will ignore indices on the column being searched
- 它们将忽略正在搜索的列上的索引
- The will (potentially) select data that is not intended, silently corrupting your results.
- 将(潜在地)选择不打算的数据，悄悄破坏您的结果。
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:
在大多数情况下，当被搜索的列上有一个调用它的函数(包括隐式的，如CAST)时，优化器必须忽略列上的索引，并在每个记录中搜索。这里有一个简单的例子:
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:
我们正在处理时间戳，大多数rdbms都倾向于将这些信息存储为某种递增值，通常是毫秒/纳秒的长或大整数计数。当前的时间是这样存储的:
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”)，是吗?事实上，有相当多复杂的数学数据需要来回转换。因此，如果您调用搜索列上的任何提取/日期部分函数，服务器必须执行所有这些运算，以确定是否可以将其包含到结果中。在小表上，这不是问题，但随着所选行的百分比减少，这将变得越来越大。在这种情况下，你是第二次问大约一个月……好吧，你懂的。
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 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:
如果只有一个月，@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 current month. 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.
因此，所有的日期操作只在一个值上执行一次;优化器可以*使用索引，并且不会包含不正确的数据。
#3
12
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
#4
8
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的解决方案，这样您就可以从yyyyy - mm -01上得到上个月的结果
#5
2
One way to do it is using the DATEPART function:
一种方法是使用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 GETDATE and DATEADD as well:
将在4月份返回所有日期。对于上个月(即前一个月)，您还可以使用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()))
#6
2
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()))
#7
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()))
#8
1
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.
这是对mrdenny解决方案的又一次升级。它给出了前一个月的最后一天。
#9
1
declare @PrevMonth as nvarchar(256)
SELECT @PrevMonth = DateName( month,DATEADD(mm, DATEDIFF(mm, 0, getdate()) - 1, 0)) +
'-' + substring(DateName( Year, getDate() ) ,3,4)
#10
1
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);
#11
0
In Sql server for last one month:
在Sql server上一个月:
select * from tablename
where order_date > DateAdd(WEEK, -1, GETDATE()+1) and order_date<=GETDATE()
#12
0
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
#13
0
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)
#14
0
WHERE
date_created >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND date_created < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
#15
0
I'm from Oracle env and I would do it like this in Oracle:
我来自Oracle env，我会在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.
想法:我正在运行上个月的计划报告(从第一天到最后一天，而不是窗口)。这可能是索引不友好，但是Oracle有快速数据处理。在MS SQL中有类似的简单和简短的方法吗?对甲骨文公司的人来说，分别比较年份和月份的答案似乎很愚蠢。
#16
0
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)
#17
0
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
然后加上WHERE语句
Month DATEADD(day,Created_Date,'1971/12/31') = month(getdate())-1