Date Filteration In SQL

Usually we need to pass the date which is typically in the format "YYYY-MM-dd" , but while converting this into datetime by using CONVERT(DATETIME,@ToDate,121) where to @ToDate ='2012-01-18' we get the result as '2012-01-18 00:00:00.000'. Because of this when you use this in BETWEEN operator like below to filter the records

select * from @tblVar where CONVERT(VARCHAR(10),LogDate,111) between  
CONVERT(VARCHAR(10),'2011/05/25',111) and  
CONVERT(VARCHAR(10),'2011/05/27',111) 

You will get only the reocrds which falls in between 2011-05-25 00:00:00.000 and 2011-05-27 00:00:00.000, because of this you will not get the records in the 2011-05-27 which hour-minute part is > 0. So to find all the records which falls in between 2 dates you have to use the below one.

CREATE PROCEDURE [dbo].[sampleReport]
(
@FromDate    VARCHAR(30) ,
@ToDate        VARCHAR(30)
)
AS

Declare @CurrentToDate datetime
Declare @DayAfterCurrentToDate datetime
Set @CurrentToDate = CONVERT(DATETIME,@ToDate,121)
set @DayAfterCurrentToDate = DateAdd(day, 1,@CurrentToDate)

select * from Table p
WHERE
P.DateField >= CONVERT(DATETIME,@FromDate,121) and 
P.DateField < @DayAfterCurrentToDate