July 31, 2012 at 8:22 am
Hello All,
I'm a newbie here and with SSRS and need your help. I'm trying to create a report that will have begin (FirstDate) and end date (LastDate) fields, along with a midpoint date input (MidpointEvalDate) field (which needs to fall between these 2 dates). There will also be other fields in the report, not needing to be mentioned at this point. The report has a dataset which includes these 3 fields. In the dataset properties, the following query is specified for both the FirstDate and LastDate fields:
select
Case
-- Run on the first of the year
When convert(varchar,getdate(),112) = cast(DATEPART(YYYY,getdate()) as varchar) + '0101' then
cast(convert(varchar,DATEADD(yy,-1,getdate()),110) as datetime)
-- Run at the beginning of a new quarter this will be scheduled for the 2nd of the month
When DATEPART(M,getdate()) in (1,4,7,10) and DATEPART(D,GETDATE()) = 2 then
dateadd(mm,datediff(mm,0,getdate())- 3,0)
Else
dateadd(mm,datediff(mm,0,getdate())+1,0)
end as [FirstDate]
, Case
-- Run on the first of the year
When convert(varchar,getdate(),112) = cast(DATEPART(YYYY,getdate()) as varchar) + '0101' then
dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate())-0,0))
-- Run at the beginning of a new quarter this will be scheduled for the 2nd of the month
When DATEPART(M,getdate()) in (1,4,7,10) and DATEPART(D,GETDATE()) = 2 then
dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate())-0,0))
Else
dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate())+2,0))
end as [LastDate]
Can anyone please advise on how I can incorporate the MidpointEvalDate into all this? Thanks for your help!
August 2, 2012 at 7:35 am
It really doesn't look like this query will work when you get to the next year.
My suggestion (without really knowing what/why you are doing your dates that way) is it would be easier to set your dates to variables, and even easier if you had a calendar table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply