CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008
LaLa
LaLa

Reputation: 301

SSRS DateTime Parameter Force Time

I have an SSRS report that lets the user pass a start time and end time into a query.

The start time works perfectly as it automatically selects 00:00:00 for the time.

However the end time also selects the same time and I would like it to auto select 11:59:59.

Is this possible?

Thanks in advance

Upvotes: 2

Views: 2032

Answers (1)

GarethD
GarethD

Reputation: 69819

There should very rarely be a need for setting a date parameter with a time of 23:59:59, usually when this is the case it is because you are using something like:

WHERE DateTimeField BETWEEN @DateTime1 AND @DateTime2;

Which is best avoided, Aaron Bertrand has gone into far more detail than I could in an answer here in his article What do BETWEEN and the devil have in common?, but the long and the short of it is that when working with DATETIME it is almost always preferential to use something like

WHERE DateField >= @DateTime1
AND DateField < @DateTime2;

Although this can cause some confusion to users who are expecting that if they choose and end date parameter of 15th August 2013, it will include data from that date, so you may need to use:

WHERE DateField >= @DateTime1
AND DateField < DATEADD(DAY, 1, @DateTime2);

Upvotes: 3

Related Questions