需要根据开始日期输入获取数据

I'm trying to retrieve data based on my beginning date input. If the user selects a beginning date of 11-27-2011 and the end date is 12/04/2011, then the returned date for my data set will be for the entire month of November. If they select a beginning date of 10/01/2011 then my data set will return data from January 1 through the current date.

我正在尝试根据开始日期输入检索数据。如果用户选择开始日期为11-27-2011且结束日期为12/04/2011,则我的数据集的返回日期将是整个11月。如果他们选择开始日期为10/01/2011,那么我的数据集将返回从1月1日到当前日期的数据。

I have managed to get the 2nd/3rd statements working but when I try to add in the filter for retrieving data from January 1 it errors out(the first IF statement in the query). The first IF statement checks to see if the @BeginningDate is less than the month prior. If it is THEN it should retrieve the years data, else it goes on from there. Here is my query for the dates:

我设法让第二/第三语句工作,但当我尝试添加过滤器以从1月1日检索数据时,它出错(查询中的第一个IF语句)。第一个IF语句检查@BeginningDate是否小于前一个月。如果它是那么它应该检索年份数据,否则它从那里继续。这是我对日期的查询:

Declare @startdate  datetime
Declare @enddate  datetime
Declare @BeginningDate datetime

set @BeginningDate = '12-01-2011'

IF Month(@BeginningDate) < (Month(GETDATE())-1)
BEGIN
set @startdate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
set @enddate = dateadd(day, datediff(day, 0, getdate()), 0)
END

IF Month(@BeginningDate) = (Month(GETDATE())-1)
BEGIN
set @startdate = dateadd (mm,-1,DATEADD(dd,-(DAY(DATEADD(mm,1,convert(varchar(10),getdate(),111)))-1),DATEADD(mm,0,convert(varchar(10),getdate(),111))))--BEGINNING OF PRIOR MONTH
set @enddate =DATEADD(dd,-1,DATEADD(mm, DATEDIFF(m,0,convert(varchar(10),getdate(),111)),0))--END OF PRIOR MONTH
END

ELSE
IF Month(@BeginningDate) = (Month(GETDATE())
BEGIN
set @startdate = dateadd(month, datediff(month, 0, dateadd(day, datediff(day, 1, getdate()), 0)), 0)--BEGINNING OF CURRENT MONTH
set @enddate = dateadd(day, datediff(day, 0, getdate()), 0)--THROUGH CURRENT MONTH (TODAY)
END

As I've stated, I can manage to get the query to work with two of the statements but when I added in the first IF and then parsed it I get this message:

正如我所说的,我可以设法让查询与两个语句一起工作,但是当我在第一个IF中添加然后解析它时,我收到以下消息:

Line 15 is the 'BEGIN' in the last ELSE statement. Am I trying to do too many IF/ELSE statements? I've never had to do this before as far as this many date parameters as well as using them in an IF/ELSE.

第15行是最后一个ELSE语句中的'BEGIN'。我试图做太多的IF / ELSE声明吗?在此之前,我从来没有必要这么多日期参数以及在IF / ELSE中使用它们。

Thanks in advance!

提前致谢!

2 个解决方案

#1

Looks like the line IF Month(@BeginningDate) = (Month(GETDATE()) is missing a )

看起来像IF Month(@BeginningDate)=(Month(GETDATE())缺少a)

change it to this

改变它

IF Month(@BeginningDate) = (Month(GETDATE())) and see if it works now

IF Month(@BeginningDate)=(Month(GETDATE()))并查看它是否现在有效

#2

I believe your parentheses are unmatched - in your last block, try

我相信你的括号是无与伦比的 - 在你的最后一个街区,试试吧

IF Month(@BeginningDate) = (Month(GETDATE()))

or

IF Month(@BeginningDate) = Month(GETDATE())

instead of the final

而不是最后的

IF Month(@BeginningDate) = (Month(GETDATE())