Hi Cpatil,
While working with datetime, when you compare the value must have time value, other wise sqlserver ignore the time and give compare based on only dates.
This is the solution of your query : DECLARE @T TABLE (id int identity(1,1), CarParkingStartTime datetime, CarParkingEndTime datetime , CarNo varchar(50))
Insert into @T values
( '01/09/2017 14:10:00', '01/10/2017 2:10:00', '1' ),
( '01/10/2017 8:10:00', '02/10/2017 6:10:00', '2' ),
( '02/10/2017 14:10:00', '02/10/2017 20:10:00', '1' ),
( '02/10/2017 8:10:00', '03/10/2017 6:10:00', '2' ),
( '02/10/2017 23:10:00', '03/10/2017 2:10:00', '1' ),
( '01/10/2017 18:10:00', '02/10/2017 10:10:00', '3' ),
( '03/10/2017 14:10:00', '04/10/2017 20:10:00', '1' )
select * from @t order by carno, CarParkingStartTime
DECLARE @Date datetime = '02/10/2017' , @datestart datetime , @dateend datetime
/*The below query give wrong answer*/
--select * from @t where @date between CarParkingStartTime and CarParkingEndTime order by carno, CarParkingStartTime
Select @datestart = cast (@date + ' 08:00:000' as datetime), @dateend = cast ( dateadd(d, 1, @date ) + ' 07:59:000' as datetime)
select @datestart , @dateend, *
from
@t
where
(CarParkingStartTime between @datestart and @dateend )or
(CarParkingEndTime between @datestart and @dateend)
order by
carno, CarParkingStartTime
Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator