Ola L Martins-329921 (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...)
I'm afraid I don't understand you.
WHERE MyDate BETWEEN '20111101' AND '20111130' will not return everything for november - it will return everything for november 1 through 29, plus everything that happened at november 30, exactly midnight.
WHERE MyDate BETWEEN '20111101' AND '20111201' will also not return everything for november - it will return everything for november, plus everything that happened at december 1, exactly midnight.
The only correct code to return everything for november, nothing less, nothing more, is to use seperate >= and < tests:
WHERE MyDate >= '20111101' AND MyDate < '20111201'