none
Date range filter RRS feed

  • Question

  • Hi,

    When running the query end date to be used for date of the range desired ex 02/1/2010 to 03/01/2010 insted of 02/01.2010 to 02/28/2010.

    Ex: Suppose i want report month of Feb, But I am selecting upto march 1st then i will get FEB month. Now i want if i Select 1st to 28th data.

    Please help this

    Friday, June 26, 2020 5:23 AM

Answers

  • For datetime data types - you should use an open-interval range:

    WHERE datecolumn >= @startDateRange
    AND datecolumn < @endDateRange
    

    Notice the < for the end date...for that you want the next day at midnight.  For example:

    WHERE datecolumn >= '20200201 00:00:00.000'
    AND datecolumn < '20200301 00:00:00.000'

    If you are passing parameters - say from SSRS, then you want to pass in the end date to be used and strip the time.  Then add one day:

    Set @endDateRange = dateadd(day, datediff(day, 0, @endDateRange), 0);
    
    WHERE datecolumn >= @startDateRange
    AND datecolumn < dateadd(day, 1, @endDateRange)


    Jeff Williams

    • Proposed as answer by Naomi N Monday, June 29, 2020 4:39 PM
    • Marked as answer by Priyanka1592 Thursday, July 2, 2020 1:16 PM
    Monday, June 29, 2020 4:36 PM
  • Hi Priyanka1592,

    Did the answers above help you?

    Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and encourage the community member to keep working on your issues.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Priyanka1592 Thursday, July 2, 2020 1:17 PM
    Tuesday, June 30, 2020 9:24 AM

All replies

  • Sorry, but I don't understand what you mean, may can you explain it more detail e.g. with an example, please?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 26, 2020 5:41 AM
  • When running the query end date to be used for date of the range desired ex 02/1/2010 to 03/01/2010 insted of 02/01.2010 to 02/28/2010.

    Ex: Suppose i want report month of Feb, But I am selecting upto march 1st then i will get FEB month. Now i want if i Select 1st to 28th data.

    Regards,

    Priyanka

    Friday, June 26, 2020 6:27 AM
  • hi

    From my under standing you need the format of date MM/dd/yyyy .Try the bellow query 

    SELECT FORMAT (datecolumn, 'MM/dd/yyyy') as [date]  from tablename

    Thanks and Regards

    Laxmidhar sahoo

    Friday, June 26, 2020 11:26 AM
  • Hi Priyanka1592,

    You can try to add date parameter to filter date you desired.

    Please refer to the following article which might help:

    Add a Date Range Dataset in SQL Server Reporting Services

    How to get the range of Values in ssrs date parameter

    Report Parameters

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 29, 2020 6:10 AM
  • For datetime data types - you should use an open-interval range:

    WHERE datecolumn >= @startDateRange
    AND datecolumn < @endDateRange
    

    Notice the < for the end date...for that you want the next day at midnight.  For example:

    WHERE datecolumn >= '20200201 00:00:00.000'
    AND datecolumn < '20200301 00:00:00.000'

    If you are passing parameters - say from SSRS, then you want to pass in the end date to be used and strip the time.  Then add one day:

    Set @endDateRange = dateadd(day, datediff(day, 0, @endDateRange), 0);
    
    WHERE datecolumn >= @startDateRange
    AND datecolumn < dateadd(day, 1, @endDateRange)


    Jeff Williams

    • Proposed as answer by Naomi N Monday, June 29, 2020 4:39 PM
    • Marked as answer by Priyanka1592 Thursday, July 2, 2020 1:16 PM
    Monday, June 29, 2020 4:36 PM
  • Hi Priyanka1592,

    Did the answers above help you?

    Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and encourage the community member to keep working on your issues.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Priyanka1592 Thursday, July 2, 2020 1:17 PM
    Tuesday, June 30, 2020 9:24 AM