SQL Between

SQL Between Example One

Q: What thespians last names are between "Nichols" and "Nickerson"?

 Select Thespian_Last_Name, Thespian_First_Name
    From Thespian_Table
   Where Thespian_Last_Name between "Nichols" and "Nickerson";




 Nicholson, Jack
 Nicholson, Nick

Note: With some DBMSs a thespian with the last name of "Nichols" or "Nickerson" is not listed because the between only selects fields that are between the test values and exclude the test values. With other DBMSs a thespian with the last name of "Nichols" or "Nickerson" are listed because the between selects fields including the test values. So, it's important to check the database that you are using to see how it treats between.

SQL Between Example Two

Q: What movies did Jack Nicholson make between "1995" and "1999"?

 Select Film_Year, Film_Title
   From Film_Table
  Where Film_Year between "1994" and "2000"
    and Film_Title in
     (Select Film_Title
        From Thespian_Film_Table
       Where Thespian_Last_Name  = "Nicholson"
         and Thespian_First_Name = "Jack");

 1995, Mars Attacks
 1997, As Good As It Gets

Note: A film with the year of 1994 or 2000 is not listed in this example BUT- in some DBMSs between only selects fields that are between but not equal to. With other DBMSs between selects as well between including equal to. So, it's important to check the database you are using to see how it treats between.
Special Thanks: to Peter Willadt for reminding me of the above.

Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.

Comment by TheMadProfessor on 2012-09-12 Rate this Comment

In the sample tables, Film_Year is a string, not a number, which is why the samples show the values of the BETWEEN clause in quotes. In your case, since your field is a date or date_time type, the values of your BETWEEN clause must be formatted appropriately.

The exact values required would depend on two things. First, whether the values themselves are intended to be inclusive or exclusive and second, whether the DBMS handles the values of a BETWEEN exclusively or inclusively. For example:

1995-1999 inclusive w/ inclusive DBMS - BETWEEN '1995/01/01' AND '1999/12/31'
1995-1999 exclusive w/ inclusive DBMS - BETWEEN '1996/01/01' AND '1998/12/31'
1995-1999 inclusive w/ exclusive DBMS - BETWEEN '1994/12/31' AND '2000/01/01'
1995-1999 exclusive w/ exclusive DBMS - BETWEEN '1995/12/31' AND '1999/01/01'

Comment by archive on 2007-09-19 Rate this Comment

Is it possible to select records using the 'between' method, when comparing dates which are formatted mm/dd/yyyy?

I've seen your sample where you select between two whole numbers, but if I use this it doesn't appear to select anything even though all of the dates are formatted correctly and there definitely are records with dates that fall between the two specified in the search.