Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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()
share|improve this question

migrated from Jul 11 '12 at 18:59

This question came from our site for professional and enthusiast programmers.

How does this get migrated to DBA? This is purely a programming question. – Yuck Jul 11 '12 at 23:44
@Yuck SSRS/SSAS/BI stuff is all on-topic here. – JNK Jul 12 '12 at 1:08

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))
share|improve this answer

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()))

share|improve this answer
You can lose the last second of the day doing this if your data has milliseconds. – Sam Jul 12 '12 at 16:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.