sqlsql-servert-sqlwhere-clausebetween

SQL : BETWEEN vs <= and >=


In SQL Server 2000 and 2005:

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)


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'