locked
How to Input end of day in SSRS RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I’ve built a report within SQL Services Reporting Services 2005 (SSRS) that pulls its data based on a date range, chosen through start and end date parameters.

    The problem is, on @EndDate  parameter. If I choose Nov 08th, it’s actually choosing Nov 08th 12:00:00 AM. This means if there’s some data that is between 12:00AM and 11:59 PM, it wouldn’t be selected.

    I needed a way to have my user select Nov 08th, but have it return values for end of day Nov 08th 11:59:59 PM  

    I found this but not useful in my case: http://faultbucket.ca/2011/04/ssrs-get-date-parameter-as-end-of-day/ 
    I used the same wording as in the post

    Thanks in Advance

     

    • Edited by ABCD0008 Tuesday, November 8, 2011 6:57 PM
    Tuesday, November 8, 2011 6:55 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Try this =DateAdd("d",1,Parameters!@EndDate.Value)
    • Proposed as answer by Naomi N Tuesday, November 8, 2011 7:57 PM
    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 7:57 PM
    Tuesday, November 8, 2011 6:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It took me a couple weeks to get it where I wanted it.

     

    Mine is done as a filter on a table to filter out dates that you dont want, so

     

    CreatedDate

    BETWEEN

    @StartDate and =Code.ConvertEndDate(Parameters!EndDate.Value)

     

    where the code goes like this

     

    Function ConvertEndDate(EndDate As Date) As Date
        Dim AEndDate AS Date
        Dim BEndDate As Date
        Dim CEndDate As Date
        AEndDate = EndDate
        BEndDate = DATEADD("d", 1,  AEndDate )
        CEndDate = DATEADD("s", -1, BEndDate )
    Return CEndDate
    End Function

    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 7:57 PM
    Tuesday, November 8, 2011 7:28 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You need to use this condition

    TransDate >=@StartDate and TransDate < @EndDate (where @EndDate is calculated as next date).

    Please take a moment to read this blog post explaining how to deal with datetime values

    Bad habits to kick : mis-handling date / range queries
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 8:12 PM
    Tuesday, November 8, 2011 7:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try:

    WHERE

    (CAST(floor(CAST(<Your Date Field> AS Float)) AS datetime) BETWEEN '11/08/2011' AND '11/08/2011'

     

     

    )


    Expect different results doing the same is madness
    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 8:13 PM
    Tuesday, November 8, 2011 8:03 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Try this =DateAdd("d",1,Parameters!@EndDate.Value)
    • Proposed as answer by Naomi N Tuesday, November 8, 2011 7:57 PM
    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 7:57 PM
    Tuesday, November 8, 2011 6:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    =DateAdd(

    "d", -1.0 * DatePart("d", Today), Today)  is what I have now for my @EndDate

     

    Tuesday, November 8, 2011 7:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Shahfaisal,

    If I use the one you suggested, I am getting Nov 09th 12:00:00 AM but not  Nov 08th 11:59:59 PM 

    Thanks

    Tuesday, November 8, 2011 7:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It took me a couple weeks to get it where I wanted it.

     

    Mine is done as a filter on a table to filter out dates that you dont want, so

     

    CreatedDate

    BETWEEN

    @StartDate and =Code.ConvertEndDate(Parameters!EndDate.Value)

     

    where the code goes like this

     

    Function ConvertEndDate(EndDate As Date) As Date
        Dim AEndDate AS Date
        Dim BEndDate As Date
        Dim CEndDate As Date
        AEndDate = EndDate
        BEndDate = DATEADD("d", 1,  AEndDate )
        CEndDate = DATEADD("s", -1, BEndDate )
    Return CEndDate
    End Function

    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 7:57 PM
    Tuesday, November 8, 2011 7:28 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You need to use this condition

    TransDate >=@StartDate and TransDate < @EndDate (where @EndDate is calculated as next date).

    Please take a moment to read this blog post explaining how to deal with datetime values

    Bad habits to kick : mis-handling date / range queries
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 8:12 PM
    Tuesday, November 8, 2011 7:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try:

    WHERE

    (CAST(floor(CAST(<Your Date Field> AS Float)) AS datetime) BETWEEN '11/08/2011' AND '11/08/2011'

    )

     

    Tuesday, November 8, 2011 8:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try:

    WHERE

    (CAST(floor(CAST(<Your Date Field> AS Float)) AS datetime) BETWEEN '11/08/2011' AND '11/08/2011'

     

     

    )


    Expect different results doing the same is madness
    • Marked as answer by ABCD0008 Tuesday, November 8, 2011 8:13 PM
    Tuesday, November 8, 2011 8:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Why do you want to make something so complicated and with a chance to return wrong data? Please take a look at the above quoted blog.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, November 8, 2011 8:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If you wanted to do this in SQL Server on the query side, you could do: 

    WHERE <whichever_date> BETWEEN @StartDate AND dateadd(minute, 1440, @EndDate) 

    ;)


    • Edited by Danimal_01 Wednesday, April 24, 2019 10:58 PM
    Wednesday, April 24, 2019 10:55 PM