SQL 上个月的 GETDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1114592/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
GETDATE last month
提问by Jeremy F.
I am trying to list last a website's statistics. I listed Last 30 days with;
我试图列出最后一个网站的统计数据。我列出了过去 30 天；
CONVERT(VARCHAR(10), S.DATEENTERED, 101)
BETWEEN
CONVERT(VARCHAR(10), GETDATE()-30, 101)
AND
CONVERT(VARCHAR(10), GETDATE(), 101)
and this month with;
本月与；
RIGHT(CONVERT(VARCHAR(10), S.DATEENTERED, 103), 7) =
RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7)
but I have no idea what query to use for last month. I tried with;
但我不知道上个月要使用什么查询。我试过;
RIGHT(CONVERT(VARCHAR(10), S.DATEENTERED, 103), 7) =
RIGHT(CONVERT(VARCHAR(10), GETDATE()-1, 103), 7)
Did not work.
不工作。
回答by ahsteele
Dates are always a joy to work with in any programming language, SQL not excluded.
在任何编程语言中使用日期总是一种乐趣，不排除 SQL。
To answer your question to find all records that occurred last month
回答您的问题以查找上个月发生的所有记录
select S.DATEENTERED
,*
from sometable S
where S.DATEENTERED
between dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())), 0)
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())) + 1, 0))
order by 1
To expand the best means for getting records within a certain time-frame is by utilizing the datediff function, dateadd function, and the between condition in the where clause.
扩展获取特定时间范围内记录的最佳方法是利用 datediff 函数、dateadd 函数和 where 子句中的 between 条件。
select 'howdy'
,getdate()
where getdate()
between dateadd(mm, 0, 0)
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(mm,-1,getutcdate())) + 1, 0))
The above code will result in no records returned because it is checking to see if today's date is between 1900-01-01 00:00:00.000 and the last possible recorded date of last month (the last day and 23:59:59.997 - SQL Server DATETIME columns have at most a 3 millisecond resolution).
上面的代码将导致没有记录返回，因为它正在检查今天的日期是否在 1900-01-01 00:00:00.000 和上个月的最后可能记录日期（最后一天和 23:59:59.997 - SQL Server DATETIME 列的分辨率最多为 3 毫秒）。
The following code will return a record as the date we are searching for is one month ago.
以下代码将返回一条记录，因为我们搜索的日期是一个月前。
select 'howdy'
,dateadd(mm, -1, getdate())
where dateadd(mm, -1, getdate())
between dateadd(mm, 0, 0)
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(mm,-1,getutcdate())) + 1, 0))
A break down of the where clause:
where 子句的分解：
WHERE getdate() -- date to check
between dateadd(mm, 0, 0) -- begin date
and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(mm,-1,getutcdate())) + 1, 0)) -- end date
Finally, a variety of dates can be ascertained in this manner here is a pretty complete list:
最后，可以通过这种方式确定各种日期，这里是一个非常完整的列表：
select dateadd(mm, 0, 0) as BeginningOfTime
,dateadd(dd, datediff(dd, 0, getdate()), 0) as Today
,dateadd(wk, datediff(wk, 0, getdate()), 0) as ThisWeekStart
,dateadd(mm, datediff(mm, 0, getdate()), 0) as ThisMonthStart
,dateadd(qq, datediff(qq, 0, getdate()), 0) as ThisQuarterStart
,dateadd(yy, datediff(yy, 0, getdate()), 0) as ThisYearStart
,dateadd(dd, datediff(dd, 0, getdate()) + 1, 0) as Tomorrow
,dateadd(wk, datediff(wk, 0, getdate()) + 1, 0) as NextWeekStart
,dateadd(mm, datediff(mm, 0, getdate()) + 1, 0) as NextMonthStart
,dateadd(qq, datediff(qq, 0, getdate()) + 1, 0) as NextQuarterStart
,dateadd(yy, datediff(yy, 0, getdate()) + 1, 0) as NextYearStart
,dateadd(ms, -3, dateadd(dd, datediff(dd, 0, getdate()) + 1, 0)) as TodayEnd
,dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0)) as ThisWeekEnd
,dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)) as ThisMonthEnd
,dateadd(ms, -3, dateadd(qq, datediff(qq, 0, getdate()) + 1, 0)) as ThisQuarterEnd
,dateadd(ms, -3, dateadd(yy, datediff(yy, 0, getdate()) + 1, 0)) as ThisYearEnd
Using the above list a range of any type can be determined.
使用上面的列表可以确定任何类型的范围。
回答by Lima
The following will find you the start of the last month:
以下将找到您上个月的开始：
-- Start of last month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime)
You would then find the start of this month, using the following, minus one.
然后你会找到这个月的开始，使用下面的减一。
-- Start of the month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime)
When I have to work with dates in SQL Server I often reference Robyn Page's SQL Server DATE/TIME Workbench. The workbench (tutorial) is well laid out and contains just about everything I have ever needed when working with dates on SQL Server.
当我必须在 SQL Server 中处理日期时，我经常参考Robyn Page 的 SQL Server DATE/TIME Workbench。工作台（教程）布局合理，几乎包含我在 SQL Server 上处理日期时所需的一切。
回答by Jeremy F.
How about this?
这个怎么样？
select DATEADD(month, -1, GETDATE())
回答by Ben Griswold
I would suggest using the first day of last month and the first day of the current month for the operation and rather than using BETWEEN use >= and <. That's my personal opinion, but I believe you will find there are performance and maintainability benefits to this approach.
我建议使用上个月的第一天和当月的第一天进行操作，而不是使用 BETWEEN 使用 >= 和 <。这是我个人的看法，但我相信您会发现这种方法具有性能和可维护性方面的优势。
Here's the sql. You will notice I've included the last day of the last month value just in case you end up going with another approach.
这是sql。您会注意到我已经包含了上个月值的最后一天，以防万一您最终采用另一种方法。
Keep in mind, these dates are based off of 12:00AM that day. In other words, getting values between6/1/2009 and 6/30/2009 won't get you what you want as all of 6/30/2009 is excluded. If you use the first day of July (7/1/2009) you are covered.
请记住，这些日期基于当天上午 12:00。换句话说，获取6/1/2009 和 6/30/2009之间的值不会得到你想要的，因为 6/30/2009 被排除在外。如果您使用 7 月的第一天 (7/1/2009)，您将得到保障。
Again, I recommend avoiding BETWEENall together as shown below. Best of luck.
同样，我建议避免BETWEEN所有在一起，如下所示。祝你好运。
Declare @LastMonthFirstDay datetime
Declare @LastMonthLastDay datetime
Declare @ThisMonthFirstDay datetime
Set @LastMonthFirstDay = DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0);
Set @ThisMonthFirstDay = DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0);
Set @LastMonthLastDay = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0));
Select * From Table
Where DateEntered >= @LastMonthFirstDay
And DateEntered < @ThisMonthFirstDay;
回答by Cody C
Try using the DATEADD function. You can add a -1 with the MONTH (mm) datepart and it should work. Here is a link
尝试使用 DATEADD 函数。您可以使用 MONTH (mm) 日期部分添加 -1，它应该可以工作。这是一个链接
回答by GSerg
where year(S.DATEENTERED) = year(dateadd(mm, -1, getdate())) and month(S.DATEENTERED) = month(dateadd(mm, -1, getdate()))
Might not be good performance-wise but you've got the idea.
在性能方面可能不是很好，但你有这个想法。
回答by Lucas Jones
Try:
尝试：
declare @lastm int
set @lastm = datepart(mm,getdate()) - 1
...
...
where datepart(mm,s.dateentered) = @lastm
回答by J.W.
GET FIRST DAY OF LAST MONTH
获取上个月的第一天
SELECT DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1,GETDATE())), '01/01/2000')
GET LAST DAY OF LAST MONTH
获取上个月的最后一天
SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,'01/01/2000',GETDATE()),'01/01/2000'))
Then search based on this range.
然后根据这个范围搜索。
