Date and time parameter In msrs report

Question

Hi I am looking to set up my report so the start date will always be say '14-09-2011 00:00:00' And the end date will always be '14-09-2011 23:59:59' Or in other words Start date will be 00:00 and end date will be 23:59 for what ever days I select.

At the moment my parameters are

Startdate: =DateAdd(DateInterval.Day, -7, Today())

End Date: =today()

Answer 1

I wouldn't worry about setting the time component. Just write your query like this instead:

WHERE SomeColumn >= @StartDate
  AND SomeColumn < DATEADD(Day, 1, @EndDate)

You have @EndDate = Today() which by default does not have a time component (or rather it's already 00:00:00). Adding a whole day to that gets you tomorrow at midnight. So the query above asks for any data on or after @StartDate but no later than tomorrow at midnight.

If you're querying a SQL 2K8+ instance you can further do this to be sure you're not erroneously using a time component:

WHERE SomeColumn >= CONVERT(Date, @StartDate)
  AND SomeColumn < CONVERT(Date, DATEADD(Day, 1, @EndDate))

Answer 2

I'm going to ignore the SQL approach since this is the obvious answer but its not clear you are even using that...

You've got your start date ie 00:00:00 since

=Today() 'returns date only

so perhaps add a day and remove a second to get 23:59:59

=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Day, 1, Today()))