SSRS adds default time to date parameter

Question

I have an SSRS report with two parameters that are dates. Of course, the date selector does not allow you to choose the time to go with dateTIME, which will make too much sense. So my idea was to give the datetime parameters a default value. For example, if I wanted the default value for a parameter to be at 8:30 a.m. today, how would I do it?

So, if today was 9/4/2013, I want to see just that: 04/09/2013 8:30.

I tried all kinds of formatting. The closest I got did this:

=CDate(Format(Today(), "MM/dd/yyyy") & " 8:30 AM") 

But I could never get seconds in order not to be displayed, because you always need to convert it back to datetime from a string, otherwise you will get an invalid type error, and CDdate ALWAYS will display seconds.

Answer 1

It looks like you are trying to format the date directly in the default parameter value, right? The fact is that CDate () converts the string to DateTime; and DateTime objects have seconds. If you do not want to display these seconds in your report, you must convert the date to a formatted string, for example:

 =Format(Parameters!yourParameter.Value, "MM/dd/yyyy hh:mm") 

To set a default time for a date parameter, you can also use something like this in the parameter's default value:

 Today().AddHours(8).AddMinutes(30) 

Answer 2

I tried everything I could find on SO to format these dates, but could not make it omit zero in the parameter as a date field or just look empty. I used SSRS 2005, so I struggled with its awkward / problematic issues.

My workaround was to add a column to my regular [DimDate] table in my database to output the dates. I added a column that was just a string representation of the [date] column. Then I created 2 new datasets that pulled out the following queries for the two defaults for my To and From defaults -

'from'

  SELECT Datestring FROM dbo.dimDate WHERE [date] = (SELECT max(date) FROM dimdate WHERE date <= GETDATE() ) 

'on'

 SELECT Datestring FROM dbo.dimDate WHERE [date] = (SELECT max(date) FROM dimdate WHERE date < DATEADD(month,-3,GETDATE())) 

Answer 3

Use this =DateAdd("s",-1,DateAdd("d",1,Parameters!dateTo.Value)) for me to adjust the time by changing the clock.