Ola L Martins-329921 (11/17/2011)
The "AND" operator will check against the date-columns seperately - not simultaneously which in the case with "BETWEEN".Using GREATER THAN and LESS THAN in the above question would actually return all the records.
BETWEEN is completely equivalent to a combination of <= and >=
SomeColumn BETWEEN @Var1 and @Var2 is exactly the same as SomeColumn >= @Var1 and SomeColumn <= @Var2. In fact, the SQL parser converts the BETWEEN into the <= and >= form during parsing (pre-execution)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability