SQL BETWEEN Operator — TutorialBrain

The BETWEEN operator is used to select within a particular range. It is used to select fields between 2 dates or 2 Numbers or 2 texts, etc.

So, this operator tests for a value within a particular range.

Note The Start and End value of the range is also included.

SELECT ProductID, Name, Color
FROM Production.Product
WHERE ProductID BETWEEN 1 AND 500;

This query is same as-

SELECT ProductID, Name, Color
FROM Production.Product
WHERE ProductID >= 1 AND ProductID <= 500;

Note Be very careful while using the BETWEEN operator with the Dates which are also having timestamps.

For Example, in Microsoft SQL Server, if you go to AdventureWorks 2019 table and consider the PurchaseOrderDetail table. If you want to get all the orders where Modified date is between 2nd Feb 2014 to 12th Aug 2015, then you code this –

SELECT PurchaseOrderID, ModifiedDate
FROM Purchasing.PurchaseOrderDetail
WHERE ModifiedDate BETWEEN '20140202' AND  '20150812';

So, you get all the records where Modified date is greater than or equal to ‘2015-08-12 00:00:00.000’ and less than or equal to ‘2015-08-12 00:00:00.000’

Look carefully at the result here that we do not have any record where date is 2015-08-12.Simply, run this –

SELECT PurchaseOrderID, ModifiedDate
FROM Purchasing.PurchaseOrderDetail;

Here, you will notice that the dates like 2015-08-12 12:25:46.483, 2015-08-12 12:25:46.470, etc are not included as these are greater than ‘2015-08-12 00:00:00.000’ because of timestamp value greater than 00:00:00.000.

If you wish to include even the date 2015-08-12 irrespective of any timestamp, then you can code –

SELECT PurchaseOrderID, ModifiedDate
FROM Purchasing.PurchaseOrderDetail
WHERE ModifiedDate BETWEEN '2014-02-02' AND  '2015-08-13';

If you have a date field which does not contain a timestamp but you wish to get the output as a datetime field, then you have to use the cast function. Consider Employee table this time –

SELECT BusinessEntityID, CAST(BirthDate AS datetime)
FROM HumanResources.Employee;

So, in short if I have a date like ‘2020-06-12’ without any timestamp but I want to get the date with timestamp, then I can code like –

SELECT CAST('2020-06-12' AS DATETIME);

The opposite of BETWEEN operator is NOT BETWEEN operator like –

SELECT PurchaseOrderID, ModifiedDate
FROM Purchasing.PurchaseOrderDetail
WHERE ModifiedDate NOT BETWEEN '2014-02-02' AND  '2015-08-12';

Tutorials for all brains!