none
Date Range

    Question

  • i have two parameters i.e. StartingDate and EndingDate. Both are date/time data type.
    What i want to do is, whenever user select any date in StartingDate, EndingDate only displays dates from the same year which the user selected in StartingDate.
    e.g. if user selects 05/05/2008 in Starting Date, EndingDate should only display date for the year 2008.

    Is that doable?
    creativity..
    Sunday, September 13, 2009 3:03 AM

Answers

  • 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 follow 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.

    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 < 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 #DateList

    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Sunday, September 13, 2009 5:11 AM
    Moderator