CODEWITHSUNDEEP
sql-serverssrs-2008-r2
mrbungle
mrbungle

Reputation: 1931

Ignore DATETIME parameter in where clause

I have some preset date parameters ('last week', 'two weeks ago', etc) in SSRS. I want to also have an 'All' parameter that would ignore the 

and createdon bewtween @FromDate and @ToDate

in the where clause and return everything regardless of date.

I've tried to build up some dynamic SQL but the overall query is quite large with many unions and became a mess unto itself. Is there a way to build a case statement here to simplify what I'm trying to achieve?

Upvotes: 0

Views: 200

Answers (1)

ChrisV
ChrisV

Reputation: 1309

As an alternative to SeanLange's suggestion that is more extensible, you can just do this:

AND createdon BETWEEN ISNULL(@FromDate, createdon) AND ISNULL(@ToDate, createdon)

Then to ignore one or both of these parameters, simply pass NULL in. The comparison between createdon and itself always succeeds, so the clause is always true.

Upvotes: 1

Related Questions