oops, this:

set @EndDate = convert(varchar(19), @BeginDate, 112) + ' 23:59:59.997'

should be this:

set @EndDate = convert(varchar(6), @BeginDate, 112) + ' 23:59:59.997'

Tushar,

I don't belive the BETWEEN operation is an issue any longer. It was a carryover optimizer snafu from the Sybase days that found its way into earlier SQL Server versions.

Append ' 23:59:59.997'...

Have you scheduled a wizard-generated DB Maintenance Plan that backs up the database or log at the same time? I believe these plans default to running the "DB Integrity Check"...