CODEWITHSUNDEEP
reporting-services
user2744449
user2744449

Reputation:

SSRS adding default time to a date parameter

I have an SSRS report with two parameters that are datetimes. Of course the date selector doesn't allow you to select times to go with the dateTIME, that would make too much sense. So my idea was to give the datetime parameters a default time. For example, if I wanted the default value of a parameter to be today's date at 8:30 AM, how would I do that?

So if today was 9/4/2013 I want to see exactly this: 09/04/2013 8:30 AM.

I have tried all kinds of formatting. The closest I got was doing this:

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

But I have never been able to get the seconds to not show up because you always have to convert this back to a datetime from a string, otherwise you get an invalid type error, and CDdate ALWAYS displays the seconds.

Upvotes: 5

Views: 34940

Answers (3)

Haider Ali
Haider Ali

Reputation: 13

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

Upvotes: 0

Chris Wood
Chris Wood

Reputation: 575

I tried everything I could find on SO to format these dates but couldn't get it to drop the zero's in the parameter as a date field or just appear blank. I was using SSRS 2005 so was struggling with its clunky / buggy issues.

My workaround was to add a column to my custom [DimDate] table in my database to pull dates. I added a column that was simply a string representation of the [date] column. I then created 2 new Datasets that pulled in the following queries for 2 defaults for my 'To' & 'From' date defaults -

'from' 

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

'to'

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

Upvotes: 1

rla4
rla4

Reputation: 1246

It seems like you're trying to format the date directly in the parameter's default value, is that correct? The thing is, CDate() converts a string into a DateTime; and DateTime objects do have seconds. If you don't want to display those seconds in your report, you should convert the date into a formatted string, such as:

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

In order to set a default time to your date parameter, you could also use something like this in the parameter default value:

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

Upvotes: 8

Related Questions