SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Report Parameter Date Range

A forum poster wanted to use a StartDate parameter to limit the range of dates available in a second parameter to that date through the end of the same year.

This can be done if you would like to use drop-down list of dates. To do this, use a query to return a string type column with the date that you can convert back to a date. When you use a date time type parameter, a standard date picker control is used, which has the capability of selecting any date beween 1-1-1753 and 12-31-9998.
If you would like to use the first technique, the following query will work to populate the parameter drop-down list. You need to manually set the @StartDate query parameter to a report parameter to pass in this value.

< Convert(Date, Convert(char(4), Year(@StartDate)) + '-12-31')
	set @TheDate = DateAdd(Day, 1, @TheDate)
	insert into #DateList Values(Convert(char(10), @TheDate, 101), @TheDate)

select * from #DateList
drop table #DateList
create table #DateList (DateString char(10), DateValue date) declare @TheDate Date set @TheDate = @StartDate insert into #DateList Values(Convert(char(10), @TheDate, 101), @TheDate) While @TheDate

MSDN Forum thread: Date Range

Weblog by Paul Turley and SQL Server BI Blog.


No comments.

Leave a Comment

Please register or log in to leave a comment.