In SQL Server 2000 and 2005:
- what is the difference between these two
WHERE
clauses? - which one I should use on which scenarios?
Query 1:
SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'
Query 2:
SELECT EventId, EventName
FROM EventMaster
WHERE EventDate >='10/15/2009'
AND EventDate <='10/18/2009'
(Edit: the second Eventdate was originally missing, so the query was syntactically wrong)
Best Answer
They are identical:
BETWEEN
is a shorthand for the longer syntax in the question that includes both values (EventDate >= '10/15/2009' and EventDate <= '10/19/2009'
).Use an alternative longer syntax where
BETWEEN
doesn't work because one or both of the values should not be included e.g.(Note
<
rather than<=
in second condition.)Dealing with DATETIME
When dealing with
DATETIME
(vs.DATE
) the result might not be intuitive however. If EventDate happens to be aDATETIME
the comparison is done with the time (!)EventDate BETWEEN '10/15/2009' AND '10/18/2009'
will in fact become:
EventDate BETWEEN '2009-10-15 00:00' and '2009-10-18 00:00'
as no time has specified. This will effectively exclude everything on 10/18/2009.
The proper expression in that case would be:
EventDate BETWEEN '2009-10-15 00:00' and '2009-10-18 23:59:59'