SQL : BETWEEN vs <= and >=
between, sql, sql-server, t-sql, where-clause
Solution
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.
Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'
(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 a `DATETIME` 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'`
Problem
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)