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

0
votes

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.

2
reporting-servicesssrs-2012

2 Answers

0
votes

You need to have 2 parameters, @fromDate, @toDate with datetime data type.

Your query still valid:

When you input @toDate and @fromDate use the following format: dd/mm/yyyy hh:mm:ss

using your previous example input for @toDate would be: 10/01/2019 20:00:00 and input for @toDate would be: 11/01/2019 06:00:00

0
votes

I have used this statment and worked perfectly.

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

Regards, Jamila