set date range to all records on dates parameters

Question

set date range to all records on dates parameters

Victor Hache 0

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

0 comments

1 answer

Your answer

Answer 1

Marcin Policht 92,045 MVP Volunteer Moderator

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

Erland Sommarskog 134.6K 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.)
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-06-02T04:17:40.2866667+00:00

Hi @Victor Hache ,

Thank you to the community members for the helpful suggestions.

Just following up to see if you were able to try either of the proposed approaches. Did setting the parameter defaults to the minimum and maximum dates, or allowing blank date parameters, help achieve the desired result?

Please let us know the outcome, and if you're still experiencing issues, share any additional details so the community can assist further.

Thanks,
Akhil.
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-06-11T06:32:14.2466667+00:00

Hi @Victor Hache ,

Thank you to the community members for their suggestions.

I'm following up to check whether you were able to test the proposed approaches and if they resolved the issue.

If you're still experiencing problems, please share any error messages, screenshots, or details about the behavior you're seeing. An update on your current status would help the community determine whether additional troubleshooting is needed.

We look forward to hearing from you.

Thanks,
Akhil.

Share via

set date range to all records on dates parameters

1 answer

Your answer