none
Date only parameter

    Question

  • In BIDS for SSRS (SQL Server 2008, not R2) I'm trying to show dates only in my parameter drop-down.

    The underlying data type (both in table and subsequent view) is Date. I've got a separate dataset specifically for this parameter:

    SELECT  CensusDate.CensusDate FROM    CensusDate WHERE (CensusDate <= GetDate()) ORDER BY CensusDate DESC

    The datetime (only choice in terms of dates) parameter @CensusDate looks at that dataset and is linked into the main query:

    (CensusDate IN (@CensusDate))

    It works fine, but the parameter is displayed wih time, e.g. 29/02/2012 12:00:00 AM and I want it to display as a date only, e.g. 29-Feb-2012

    There is a lot on date/datetime, conversion and formatting online, but I can't seem to make it work. I can't use an expression I think, because if I do it comes back with a 'Fields cannot be used in a report parameter' and when I convert to varchar it doesn't seem to work properly.

    Any ideas anyone? I've been at this for hours and it's getting ridiculous for such a seemingly small problem...

    Thursday, March 22, 2012 4:52 AM

Answers

All replies

  • Use

    select cast(censusDate.CensusDate as date)  FROM    CensusDate WHERE (CensusDate <= GetDate()) ORDER BY CensusDate DESC

    Thursday, March 22, 2012 5:11 AM
  • SELECT replace(convert(char(11),censusDate.CensusDate,113),'','-') StrDate, CensusDate
    FROM    CensusDate
    WHERE (CensusDate <= GetDate()) ORDER BY CensusDate DESC

    Or the above, you will have to set the strDate to the label for the parameter, and CensusDate to the Value.

    A nifty function here for formats

    http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!


    • Edited by GGoldspink Thursday, March 22, 2012 5:35 AM
    • Marked as answer by A. Bettik Monday, March 26, 2012 1:25 AM
    Thursday, March 22, 2012 5:33 AM
  • Thanks - I has tried the CAST option already. I'm thinking if the data type is Date, I shouldn't have to CAST it as a Date, but I previously tried that anyway and it didn't make a difference - still showing the time.

    The second option worked, thanks for that. My previous mistake was using the string as the value, which resulted in an error. Using it solely as the label works better.

    It doesn't seem to work as the default value though? I've got the StrDate as default, but it's still asking me to 'Select a value'. When I use the CensusDate Value field I get an error, as there obviously is a conflict between date & char.

    Any suggestions on that one?

    Thursday, March 22, 2012 5:49 AM
  • Default Value should be the CensusDate you want defaulted, not the str manipulation.

    You use the same query dataset and select CensusDate as the Value?

    eg

    DECLARE @date1 datetime,
    @date2 datetime
    SET @date1 = getdate()
    SET @date2 = dateadd(day,1,getdate())

    SELECT replace(convert(char(11),@date1,113),'','-') strdate, @date1 ddate
    union all
    SELECT replace(convert(char(11),@date2,113),'','-'), @date2

    If that was your census date dataset, ddate should be your value for your selection list and default selection.  If that is what you have, I suspect something else somewhere is not liking the date.


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!


    • Edited by GGoldspink Thursday, March 22, 2012 6:37 AM
    Thursday, March 22, 2012 6:31 AM
  • I have a main dataset, based on a View and I have a parameter dataset, based on a table of census dates (for performance reasons).

    This table has the same values, as the View for the main dataset uses that table to derrive its values from. I have ensured that both the view and the underlying table have the censusdate column as data type Date.

    The StrDate and CensusDate fields used for Label and Value of the parameter come from that same parameter dataset.

    The main dataset also contains a census date field, but that is an unconverted field.

    Was that roughly what you're asking?

    Friday, March 23, 2012 4:23 AM
  • Ah, my mistake, the string confused me (easily done) and I had the parameter set as text.

    I changed it back to datetime and then it ran fine. I think the main lesson for me here is that the parameter label can be pretty much anything you want, and that the actual work behind the scenes is done with that row's corresponding value.

    Thanks again - I'll mark it as answered shortly.

    Monday, March 26, 2012 1:25 AM