GPO (11/17/2011)
There are good reasons to avoid using BETWEEN when comparing two datetimes. Particularly in the example the answer uses. You should be using the various symbols for GREATER THAN and LESS THAN.
No, I don't think you can (if not reverting to subqueries...)
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.
(Sorry, being a bit rusty I'm not entirely sure this is correct, but I've been there, done that and bought the friggin' t-shirt...)