set date range to all records on dates parameters

Victor Hache 0 Reputation points
2026-05-28T19:10:12.9933333+00:00

I want to set my date range in parameters to all dates. how can I do that in parameters settings

SQL Server Reporting Services
SQL Server Reporting Services

A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.

0 comments

Answer recommended by moderator

Erland Sommarskog 135.3K Reputation points MVP Volunteer Moderator
2026-05-28T20:10:12.4466667+00:00

To set a date range parameter to include all dates in SSRS, you can use a query to automatically find the earliest and latest dates in your database. First, create a new dataset in your report that pulls the minimum and maximum dates from your table using a query like SELECT MIN(DateColumn) AS MinDate, MAX(DateColumn) AS MaxDate FROM YourTable

Since Victor's post is so terse, it's impossible to say what he has in mind, but my gut reaction is that don't like this. I would prefer to just leave them blank. Or if you want to by default cut down the default range for the user, set the start to one week ago, and max to today. (Assuming that this make sense for the report at hand. It obviously does not make sense for something that looks at booking in the future.)

You can write your filter clause like WHERE (@StartDate IS NULL OR DateColumn >= @StartDate) AND (@EndDate IS NULL OR DateColumn <= @EndDate).

That is better written as 

WHERE DateColumn >= isnull(@StartDate, '20000101')
  AND DateColumn <= isnull(@EndDate, '99991231')

The style above is likely to prevent use of any index on DateColumn. (Which is likely to be useful if the range is narrow.)

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Marcin Policht 94,940 Reputation points MVP Volunteer Moderator
    2026-05-28T19:53:38.66+00:00

    To set a date range parameter to include all dates in SSRS, you can use a query to automatically find the earliest and latest dates in your database. First, create a new dataset in your report that pulls the minimum and maximum dates from your table using a query like SELECT MIN(DateColumn) AS MinDate, MAX(DateColumn) AS MaxDate FROM YourTable. Next, open the properties for your start date parameter, go to the default values tab, choose to get values from a query, and select the minimum date field. Do the same for your end date parameter, but select the maximum date field as the default value instead. This should make the report automatically select the widest possible date range every time it runs.

    Another potential approach is to have date parameters blank. To do this, open the parameter properties for both your start and end dates and check the box that allows null values. Then, must update the main dataset query for your report to ignore the date filter if no date is chosen. You can write your filter clause like WHERE (@StartDate IS NULL OR DateColumn >= @StartDate) AND (@EndDate IS NULL OR DateColumn <= @EndDate).

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.