Need help setting end date in SSRS

Posted on 2012-09-21
Last Modified: 2012-09-21
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?
Question by:HSI_guelph
    LVL 22

    Accepted Solution

    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


    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    This video discusses moving either the default database or any database to a new volume.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now