SQL : BETWEEN vs <= and >=

Question

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)

Answer 1

They are identical: BETWEEN is a shorthand for the longer syntax in the question.

Use an alternative longer syntax where BETWEEN doesn't work e.g.

Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/18/2009'

(Note < rather than <= in second condition.)