Date range Parameters in SSRS. Passing same date value to @StartDate and @EndDate is not returning any data

Question

SSRS Report having Data Range Parameters StartDate and EndDate pointing to DateTime column which is in format 2020-07-05 08:00:00.287

When I select Same Date say 10/05/2020 in StartDate and EndDate expecting record for that single daye but it's not returning/displaying any data. where as I check and see there's data available in the table.

Current Query: Select * from Table1 Where [DateTime]>=@StartDate AND [DateTime]<=EndDate

Also tried BETWEEN and (CONVERT(VARCHAR(20),DateTime,101), didn't work.

I want to use StartDate and EndDate as date range parameters and if I select same date(Single date) for both the Parameters it should return data from that Date. Appreciate any leads on resolving this issue.

Sample data? What date range are you using and what date are you expecting to show up? The query would also be helpful. Reading your comments again I think I see what you mean. Start and End is 10/5 and you expect to see 10/5? If your data isn't too large, I'd simply try a CAST function. So an example would be ...WHERE CAST(DateTime as DATE) BETWEEN @Start and @End — jw11432
To explain why this happens, the date is implicitly converted to datetime. So it filters on all datetimes between 2020-05-10 00:00:00 and 2020-05-10 00:00:00 — OwlsSleeping

Patrick Bacon Patrick Bacon · Accepted Answer · 2020-10-27T15:32:05

Use the DATEADD Function to Increment by 1 Day and Then Compare

In this scenario where the underlying data in the transactional date column is a DATETIME value, I would just increment the parameter by 1 and create a less than condition as follows:

    DECLARE @StartDate AS DATE
    DECLARE @EndDate AS DATE
    
    SET @StartDate = CAST(GETDATE() AS DATE)
    SET @EndDate = CAST(GETDATE() AS DATE)
    ;
    
 SELECT
    *
FROM
    favorite_table
WHERE
        1 = 1
    AND transactional_dt >= @StartDate
    AND transactional_dt  < DATEADD(d,1,@EndDate );

Date range Parameters in SSRS. Passing same date value to @StartDate and @EndDate is not returning any data

1 Answers