I overlooked that it is a STRING comparison and got the answer wrong.:-) (though writer had intention to show datetime comparison)
I updated the code as follows and executed:
DECLARE @DATE DATETIME
SET @DATE = '2011-07-31 00:00:00.001
IF (@DATE BETWEEN '2011-07-01' and '2011-07-31')
The output came 'Yes'. However when I changed the @date value to '2011-07-31 00:00:00.002
' the result was 'No'. As per my knowledge the @date value should be compared to the end date '2011-07-31 00:00:00.000'. How '2011-07-31 00:00:00.001' be less than or equal to '2011-07-31 00:00:00.000'?
I executed this on SQL Sever 2008 EE Ver. 10.0.4064.0