• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • 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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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