SQL 返回上个月和当月的第一天 @ 00:00:00
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5940839/
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
SQL return first day of last month and current month @ 00:00:00
提问by gooddadmike
I am using this code
我正在使用此代码
declare @mydate datetime select @mydate = getdate()
declare @startdate varchar(20) SELECT @StartDate = CONVERT(varchar(20),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120)
declare @enddate varchar(20) SELECT @EndDate = CONVERT(varchar(20),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120)
Select @startdate as 'Start Date', @enddate as 'End Date'
To determine the first day of the previous and current month for a report, but it gives a time based on the current time. How can I get
确定报告的上个月和当前月份的第一天，但它根据当前时间给出时间。我怎样才能得到
2011-04-01 00:00:00
&
2011-05-01 00:00:00
?
?
回答by gbn
Use the DATEADD/DATEDIFF trick:
SELECT
DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
回答by Shak Kash
You can change your last statement to the one below to achieve the results:
您可以将最后一条语句更改为以下语句以实现结果：
Select SUBSTRING(@startdate,1,11) + '00:00:00' as 'Start Date',
SUBSTRING(@enddate,1,11)+ '00:00:00' as 'End Date'
回答by Simon Mokhele
If you are using MYSQL Data base
如果您使用的是 MYSQL 数据库
# First day of the previous month
SELECT concat(LAST_DAY(NOW() - INTERVAL 1 MONTH),' ','00:00:00') as 'PreviousMonthFirstDay';
# Last day of the previous month
SELECT concat(LAST_DAY(NOW() - INTERVAL 1 MONTH),' ','00:00:00') as 'PreviousMonthLastDay';
Reference MySQL First day of previous month
回答by Diwakar
From SQL2012, there is a new function introduced called EOMONTH. Using this function the first and last day of the month can be easily found.
从 SQL2012 开始，引入了一个名为 EOMONTH 的新函数。使用此功能可以轻松找到该月的第一天和最后一天。
select DATEADD(DD,1,EOMONTH(Getdate(),-1)) firstdayofmonth, EOMONTH(Getdate()) lastdayofmonth
选择 DATEADD(DD,1,EOMONTH(Getdate(),-1)) firstdayofmonth, EOMONTH(Getdate()) lastdayofmonth
