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!
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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