reporting-services - How to add Date and Time Parameter for two different days in SSRS

Question

I am using SSRS 2012. I have an issue with my date and time parameters. I in my dataset I have [datetime], [date] and [time] columns, and parameters @fromDate, @toDate of datetime data type and @fromtime, @totime of text data type.

It works perfectly until the user chooses the the @totime to be less the @fromtime.

Example :

@fromDate = 10/1/2019 
@toDate = 11/1/2019

fromtime: 20:00 (which is 10/1/2019)
totime: 06:00 (which should be the next day 11/1/2019)

I get no data in the report and I think because the system understand it as swapping the data which is not equivalent with the query.

Where
    F.[Datetime] >= @fromDate
    AND F.[Datetime] <= @toDate
    AND Em.Name IN (@Name)
    AND F.[time] >= @fromtime
    AND F.[time] <= @totime

I expect the output to be :

5 row for employee names with the mentioned date and time who get out from 10/1/2019 20:00 to 11/1/2019 06:00 am .

but the actual output was no data.

标签： reporting-servicesssrs-2012

Answer 1

我已经使用了这个声明并且工作得很好。

 where datetime >= DATEADD(dd,DATEDIFF(dd,0,@StartDate),@StartTime) 
and datetime <=DATEADD(dd,DATEDIFF(dd,0,@EndDate)+1,@EndTime)

问候， 贾米拉