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.
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