In SQL Server 2000 and 2005:
WHERE
clauses?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)
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.)
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'