• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1135
  • Last Modified:

Need help setting end date in SSRS

I am using parameters to set the start and end dates of our business week with this code:
=DateAdd("d", -7 -(WeekDay(Today(),2))+1, Today())

Open in new window

gives me 9/10/2012 12:00:00 AM

=DateAdd("d", -1 -(WeekDay(Today(),2))+1, Today())

Open in new window

gives me 9/16/2012 12:00:00 AM
I need the end date to be 11:59:59 AM.  I can't set the end date to September 17th at midnight because hours for the 17th are clocked in at 12:00:00 AM (database set up by outside company).  I'm generating the dates dynamically, can anyone help me fix the end date please?
1 Solution
Nico BontenbalCommented:
You can use:
=dateadd("s",-1,DateAdd("d", -1 -(WeekDay(Today(),2))+2, Today()))

Open in new window

I first changed your code so it returns the 17th then I used a dateadd to substract 1 second. In your post you say you need 11:59:59AM, but I assumed that should be PM. If you actually need AM you can use this:
=dateadd("s",-43201,DateAdd("d", -1 -(WeekDay(Today(),2))+2, Today()))

Open in new window

HSI_guelphAuthor Commented:
Yes I need PM lol, I just made a typo.  Thanks for the help!  Works perfectly!

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now