SQL 如何获取上月数据和月至今数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5412019/
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 the last month data and month to date data
提问by Shahsra
Need help in writing the query to get the last month data as well as month to date data.
需要帮助编写查询以获取上个月的数据以及本月至今的数据。
If today's date is Mar 23 2011, I need to retrieve the data from last month and the data till todays date(means Mar 23 2011).
如果今天的日期是 2011 年 3 月 23 日，我需要检索上个月的数据和今天的数据（即 2011 年 3 月 23 日）。
If date is Apr 3 2011, data should consists of March month data and the data till Apr 3rd 2011.
如果日期是 2011 年 4 月 3 日，则数据应包括 3 月份的数据和截至 2011 年 4 月 3 日的数据。
Thanks,
谢谢，
Shahsra
沙赫斯拉
回答by RichardTheKiwi
Today including time info : getdate()
Today without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
Tomorrow without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)
Beginning of current month : DATEADD(month, datediff(month, 0, getdate()), 0)
Beginning of last month : DATEADD(month, datediff(month, 0, getdate())-1, 0)
so most likely
所以很可能
WHERE dateColumn >= DATEADD(month, datediff(month, 0, getdate())-1, 0)
AND dateColumn < DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)
回答by Russell Steen
Step back one month, subtract the number of days to the current date, and add one day.
后退一个月，减去当前日期的天数，再加一天。
WHERE
DateField <= GetDate() AND
DateField >= DateAdd(
mm,
-1,
DateAdd(dd, -1*DatePart(dd, GetDate())+1, GetDate())
)
To remove the time quickly, you can use this Cast( Floor( Cast( GETDATE() AS FLOAT ) ) AS DATETIME )
要快速删除时间，您可以使用此 Cast( Floor( Cast( GETDATE() AS FLOAT ) ) AS DATETIME )
So the second part would be (without time)
所以第二部分将是（没有时间）
DateField >= Cast( Floor( Cast( (DateAdd(
mm,
-1,
DateAdd(dd, -1*DatePart(dd, GetDate())+1, GetDate())
)) AS FLOAT ) ) AS DATETIME )
回答by eupton
Select Column1, Column2 From Table1
Where DateColumn <= GetDate() AND
DateColumn >= DATEADD(dd, - (DAY(DATEADD(mm, 1, GetDate())) - 1), DATEADD(mm, - 1, GetDate()))
Edit: +1 to Russel Steen. I was posting mine before I knew he had posted.
编辑：+1 给 Russel Steen。在我知道他已经发布之前，我已经发布了我的。
回答by PaulF
declare @d datetime = '2011-04-03';
declare @startDate datetime;
select @startDate =
CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,@d),113),8) AS datetime);
select @startDate;
