SQL : BETWEEN vs <= and >=

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 EventMasterwhere EventDate >= '10/15/2009' and EventDate < '10/19/2009'

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

They are the same.

One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day:

<= 20/10/2009

is not the same as:

<= 20/10/2009 23:59:59

(it would match against <= 20/10/2009 00:00:00.000)

Although BETWEEN is easy to read and maintain, I rarely recommend its use because it is a closed interval and as mentioned previously this can be a problem with dates - even without time components.

For example, when dealing with monthly data it is often common to compare dates BETWEEN first AND last, but in practice this is usually easier to write dt >= first AND dt < next-first (which also solves the time part issue) - since determining last usually is one step longer than determining next-first (by subtracting a day).

In addition, another gotcha is that lower and upper bounds do need to be specified in the correct order (i.e. BETWEEN low AND high).