Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com 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
add comment

2 Answers 2

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
add comment

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
add comment

Your Answer

 
discard

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