How to find a record between two dates and times

1

I want to find records in between date 07/05/2019 to 08/05/2019 and time 5:00PM to 4:00AM respectively i.e in between 07/05/2019 5:00PM and 08/05/2019 4:00AM
I Have Used following query but it doesnot work Please help.

select * from TagInWardMaster where
(InsertedOn >= '07/05/2019' AND InsertedOn < DATEADD(DAY, 1, '08/05/2019'))
and RIGHT(CONVERT(nvarchar,insertedon,100),7) between ' 5:00PM'and ' 4:00AM'

select * from TagInWardMaster where
(InsertedOn >= '07/05/2019' AND InsertedOn < DATEADD(DAY, 1, '08/05/2019'))
and convert(nvarchar(5),InsertedOn,108)>='17:00' and convert(nvarchar(5),InsertedOn,108)<='04:00'

2

If you just want one continuous range of time, do this:

select * from TagInWardMaster where
(InsertedOn >= '20190705 17:00' AND InsertedOn <= '20190805 04:00')

If the time in every row must be between 5PM and 4AM (i.e., not from 4AM+ to 5PM-), then do this:

select * from TagInWardMaster where
(InsertedOn >= '20190705 17:00' AND InsertedOn <= '20190805 04:00')
and CAST(InsertedOn AS time(3)) NOT BETWEEN '04:00:00.003' AND '16:59:59.997'
1 Like