SQL BETWEEN: Today's post is short, but it outlines the SQL BETWEEN Operator.

Today’s post is short, but it outlines the SQL BETWEEN Operator. I’ve been using SQL for many years and I have not used this operator before, but came across it a couple weeks back. It essentially selects values within a given range, and the values specified are included in the results (i.e. greater than or equal to, less than or equal to).

This SQL Operator works with numbers, text and dates. There is also a NOT BETWEEN Operator, which can be used to find records outside the range of values specified.

SQL Version: SQL Server 2014 (Express Edition)

Examples: SQL BETWEEN

Copy down the following code, then execute to see how the examples work!


IF OBJECT_ID('tempdb.dbo.#Employee', 'U') IS NOT NULL
  DROP TABLE #Employee;

CREATE TABLE #Employee (EmployeeID INT, LastName VARCHAR(25), VacationHours INT, HireDate Date)
INSERT INTO #Employee
SELECT 10, 'Raheem', 16, '06/04/2003' UNION ALL
SELECT 28, 'Gilbert', 21, '07/31/2000' UNION ALL
SELECT 29, 'McArthur', 19, '02/24/2003' UNION ALL
SELECT 30, 'Simon', 14, '03/02/2003' UNION ALL
SELECT 31, 'Shoop', 18, '02/05/2003' UNION ALL
SELECT 32, 'Laszlo', 23, '01/30/2003' UNION ALL
SELECT 33, 'Stahl', 17, '01/18/2003' UNION ALL
SELECT 34, 'Mohan', 15, '03/20/2003' UNION ALL
SELECT 35, 'Heidepriem', 22, '03/12/2003' UNION ALL
SELECT 36, 'Lugo', 20, '03/14/2003' UNION ALL
SELECT 37, 'Okelberry', 16, '04/08/2003' UNION ALL
SELECT 38, 'Abercrombie', 24, '02/17/2004' UNION ALL
SELECT 39, 'Dudenhoefer', 25, '03/08/2004' UNION ALL
SELECT 57, 'Miller', 10, '03/27/2003' UNION ALL
SELECT 58, 'Keil', 11, '01/06/2003' UNION ALL
SELECT 59, 'Hohman', 12, '01/25/2003' UNION ALL
SELECT 60, 'Male', 13, '02/12/2003' UNION ALL
SELECT 153, 'Feng', 15, '01/17/2003' UNION ALL
SELECT 154, 'Sam', 10, '01/24/2003' UNION ALL
SELECT 155, 'Fakhouri', 16, '02/05/2003' UNION ALL
SELECT 156, 'Sacksteder', 11, '02/12/2003' UNION ALL
SELECT 157, 'Randall', 12, '03/07/2003' UNION ALL
SELECT 158, 'Dyck', 14, '04/08/2003' UNION ALL
SELECT 159, 'Earls', 13, '03/20/2003' UNION ALL
SELECT 167, 'Johnson', 25, '01/03/2003' UNION ALL
SELECT 172, 'Ingle', 22, '02/17/2003' UNION ALL
SELECT 175, 'Koch', 23, '03/06/2003' UNION ALL
SELECT 177, 'King', 24, '03/25/2003' UNION ALL
SELECT 181, 'Hines', 20, '01/10/2003' UNION ALL
SELECT 182, 'Mirchandani', 21, '01/29/2003' UNION ALL
SELECT 183, 'Decker', 17, '02/23/2003' UNION ALL
SELECT 184, 'Chen', 18, '03/13/2003' UNION ALL
SELECT 185, 'Hesse', 19, '04/01/2003' UNION ALL
SELECT 273, 'Welcker', 10, '03/18/2005' UNION ALL
SELECT 274, 'Jiang', 14, '02/04/2005' UNION ALL
SELECT 277, 'Carson', 24, '07/01/2005' UNION ALL
SELECT 280, 'Ansman-Wolfe', 22, '07/01/2005' UNION ALL
SELECT 283, 'Campbell', 23, '07/01/2005' UNION ALL
SELECT 285, 'Abbas', 20, '04/15/2007' UNION ALL
SELECT 287, 'Alberts', 21, '05/18/2006';

SELECT * FROM #Employee;

--Values
SELECT
	*
FROM #Employee
WHERE VacationHours BETWEEN 15 AND 20;

--Dates
SELECT
	*
FROM #Employee
WHERE HireDate BETWEEN '2003-02-01' AND '2003-02-15';

--Text
SELECT
	*
FROM #Employee
WHERE LastName BETWEEN 'Gilbert' AND 'Ingle'
ORDER BY LastName;

IF OBJECT_ID('tempdb.dbo.#Employee', 'U') IS NOT NULL

DROP TABLE #Employee;

CREATE TABLE #Employee (EmployeeID INT, LastName VARCHAR(25), VacationHours INT, HireDate Date)

INSERT INTO #Employee

SELECT 10, 'Raheem', 16, '06/04/2003' UNION ALL

SELECT 28, 'Gilbert', 21, '07/31/2000' UNION ALL

SELECT 29, 'McArthur', 19, '02/24/2003' UNION ALL

SELECT 30, 'Simon', 14, '03/02/2003' UNION ALL

SELECT 31, 'Shoop', 18, '02/05/2003' UNION ALL

SELECT 32, 'Laszlo', 23, '01/30/2003' UNION ALL

SELECT 33, 'Stahl', 17, '01/18/2003' UNION ALL

SELECT 34, 'Mohan', 15, '03/20/2003' UNION ALL

SELECT 35, 'Heidepriem', 22, '03/12/2003' UNION ALL

SELECT 36, 'Lugo', 20, '03/14/2003' UNION ALL

SELECT 37, 'Okelberry', 16, '04/08/2003' UNION ALL

SELECT 38, 'Abercrombie', 24, '02/17/2004' UNION ALL

SELECT 39, 'Dudenhoefer', 25, '03/08/2004' UNION ALL

SELECT 57, 'Miller', 10, '03/27/2003' UNION ALL

SELECT 58, 'Keil', 11, '01/06/2003' UNION ALL

SELECT 59, 'Hohman', 12, '01/25/2003' UNION ALL

SELECT 60, 'Male', 13, '02/12/2003' UNION ALL

SELECT 153, 'Feng', 15, '01/17/2003' UNION ALL

SELECT 154, 'Sam', 10, '01/24/2003' UNION ALL

SELECT 155, 'Fakhouri', 16, '02/05/2003' UNION ALL

SELECT 156, 'Sacksteder', 11, '02/12/2003' UNION ALL

SELECT 157, 'Randall', 12, '03/07/2003' UNION ALL

SELECT 158, 'Dyck', 14, '04/08/2003' UNION ALL

SELECT 159, 'Earls', 13, '03/20/2003' UNION ALL

SELECT 167, 'Johnson', 25, '01/03/2003' UNION ALL

SELECT 172, 'Ingle', 22, '02/17/2003' UNION ALL

SELECT 175, 'Koch', 23, '03/06/2003' UNION ALL

SELECT 177, 'King', 24, '03/25/2003' UNION ALL

SELECT 181, 'Hines', 20, '01/10/2003' UNION ALL

SELECT 182, 'Mirchandani', 21, '01/29/2003' UNION ALL

SELECT 183, 'Decker', 17, '02/23/2003' UNION ALL

SELECT 184, 'Chen', 18, '03/13/2003' UNION ALL

SELECT 185, 'Hesse', 19, '04/01/2003' UNION ALL

SELECT 273, 'Welcker', 10, '03/18/2005' UNION ALL

SELECT 274, 'Jiang', 14, '02/04/2005' UNION ALL

SELECT 277, 'Carson', 24, '07/01/2005' UNION ALL

SELECT 280, 'Ansman-Wolfe', 22, '07/01/2005' UNION ALL

SELECT 283, 'Campbell', 23, '07/01/2005' UNION ALL

SELECT 285, 'Abbas', 20, '04/15/2007' UNION ALL

SELECT 287, 'Alberts', 21, '05/18/2006';

SELECT * FROM #Employee;

--Values

SELECT

FROM #Employee

WHERE VacationHours BETWEEN 15 AND 20;

--Dates

SELECT

FROM #Employee

WHERE HireDate BETWEEN '2003-02-01' AND '2003-02-15';

--Text

SELECT

FROM #Employee

WHERE LastName BETWEEN 'Gilbert' AND 'Ingle'

ORDER BY LastName;

Example 1: Values – Results look like the following, with Vacation Hours ranging between 15 and 20

Example 2: Dates – Results look like the following, with Hire Dates ranging between 2003-02-01 and 2003-02-15

Example 3: Text – Results look like the following, with Last Names ranging between “Gilbert” and “Ingle”

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering Qlikview SQL Server Excel VBA

March 2020
M	T	W	T	F	S	S
« Dec
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Examples: SQL BETWEEN

Example 1: Values – Results look like the following, with Vacation Hours ranging between 15 and 20

Example 2: Dates – Results look like the following, with Hire Dates ranging between 2003-02-01 and 2003-02-15

Example 3: Text – Results look like the following, with Last Names ranging between “Gilbert” and “Ingle”

Additional Content

Leave a Comment Cancel reply