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.

Answer 1

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 );