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')="" begin="" set="" @thedate="DateAdd(Day," 1,="" @thedate)="" insert="" into="" #datelist="" values(convert(char(10),="" @thedate,="" 101),="" @thedate)="" end="" select="" *="" from="" #datelist="" drop="" table="">
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.