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.
This query is same as-
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 –
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 –
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 –
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 –
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 –
The opposite of BETWEEN operator is NOT BETWEEN operator like –