SQL 如何在SQL中自动获取上个月的日期范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24832566/
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
how to get automatically previous month date range in SQL?
提问by user4778
I have 2012 SQL Server stored procedure ran automatically every fifth of the month but the problem I have is supplying previous month date range e.g. I need to have clause like ...where datein between '2014-02-01' and '2014-02-28'and next month it would change it to ...where datein between '2014-03-01' and '2014-02-31'and so on.
我有 2012 SQL Server 存储过程每五月自动运行一次，但我遇到的问题是提供上个月的日期范围，例如我需要有像...where datein between '2014-02-01' 和 '2014-02 之类的子句 -28'和下个月它会将其更改为...日期在 '2014-03-01' 和 '2014-02-31' 之间的位置，依此类推。
thanks
谢谢
回答by Dbloch
This should work
这应该工作
SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AS StartDate, EOMONTH(GETDATE(),-1) AS EndDate
To be more specific in your WHERE clause
在您的 WHERE 子句中更具体
WHERE @datein BETWEEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AND EOMONTH(GETDATE(),-1)
回答by user11658885
It is bad practice to use getdate(), instead across all application use getutcdate()
使用 getdate() 是不好的做法，而是在所有应用程序中使用 getutcdate()
WHERE @datein BETWEEN DATEADD(DAY,1,EOMONTH(GETUTCDATE(),-2)) AND EOMONTH(GETUTCDATE(),-1)
回答by Gordon Linoff
You can use getdate()and some date arithmetic. Here is a relatively easy way:
您可以使用getdate()和一些日期算术。这是一个相对简单的方法：
where datein >= cast(dateadd(month, -1, getdate() - day(getdate()) + 1) as date) and
datein < cast(getdate() - day(getdate()) + 1)
The key idea here is to subtract the day of the month and add 1 to get the first day of the current month.
这里的关键思想是减去当月的第一天并加 1 以获得当月的第一天。
回答by Sean Lange
Here is a way to do this so your dates will be greater than the beginning of this month and less than the beginning of next month.
这里有一种方法可以使您的日期大于本月初而小于下月初。
datein >= dateadd(month, datediff(month, 0, getdate()), 0) --BeginningOfThisMonth
and datein < dateadd(month, datediff(month, 0, getdate()) + 1, 0) --BeginningOfNextMonth
回答by user3011237
This worked for my current version of mysql:
这适用于我当前版本的 mysql：
SELECT DATE_ADD(LAST_DAY(DATE_SUB(current_date, INTERVAL 2 MONTH)), INTERVAL 1 DAY) AS StartDate,
LAST_DAY(DATE_SUB(current_date, INTERVAL 1 MONTH)) AS EndDate;
回答by Doug Thompson - DouggyFresh
might try this for Feb as original only went to 27th of Feb BETWEEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AND DATEADD(DAY,1,EOMONTH(GETDATE(),-1))
可能会在 2 月尝试此操作，因为原始仅在 2 月 27 日 BETWEEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) 和 DATEADD(DAY,1,EOMONTH(GETDATE(),-1))
