Hi,
Please refer below script,I hope this will help you out.
DECLARE @Temp AS TABLE(Id INT,CDate DATETIME)
INSERT INTO @Temp VALUES(1,'06/01/2016 07:00:00')
INSERT INTO @Temp VALUES(2,'06/01/2016 08:00:00')
INSERT INTO @Temp VALUES(3,'06/01/2016 08:08:00')
INSERT INTO @Temp VALUES(4,'06/01/2016 09:00:00')
INSERT INTO @Temp VALUES(5,'06/01/2016 09:09:00')
INSERT INTO @Temp VALUES(6,'08/01/2016 09:09:00')
DECLARE @FromDate DATETIME
SET @FromDate = '06/01/2016 07:00:00'
DECLARE @ToDate DATETIME
SET @ToDate = '06/01/2016 09:00:00'
-- This will return result based on Date only
SELECT * FROM @Temp
WHERE (CONVERT(VARCHAR(20),CDate,101))
Between (CONVERT(VARCHAR(20),@FromDate,101)) and (CONVERT(VARCHAR(20),@ToDate,101))
-- This will return result based on Date and Time
SELECT * FROM @Temp
WHERE CDate
Between @FromDate and @ToDate
-- This will not return any record (i.e. you have done below one)
SELECT * FROM @Temp
WHERE (CONVERT(VARCHAR(20),CDate,101))
Between @FromDate and @ToDate
I hope this will help you out.
