Hello! If you’re working with SQL Server and need to filter records based on a date range, you’re in the right place. In this article, we’ll discuss how to select data between two dates in SQL Server using best practices to ensure accuracy and performance. Date filtering is a common task when analyzing time-bound records irrespective you being SQL developer or production DBA.
Reasons to Select Data Between Two Dates:
- You want to check the successful backups happened between two dates
- What the are indexes which were part of Index maintenance in last couple of weekends
- What is the data/log file growth in a month etc.
When working with large datasets, retrieving only the relevant time-based records helps.
Microsoft SQL Server provides different methods to select data between two dates. The most common ones are as follows:
- BETWEEN Operator
- Arithmetic Operators Greater Than (>) and Less Than (<)
SELECT Data Between Two Dates in SQL Server Using BETWEEN Operator:
The BETWEEN keyword is inclusive, that means it includes both the start and end dates.
Syntax:
SELECT * FROM [TableName] WHERE [DateColumn] BETWEEN 'StartDate' AND 'EndDate' --Ensure the date format matches the column data type (YYYY-MM-DD is recommended).
Now let’s take an example. Assume you have the following data in a SalesInfo table:
You want to extract the sales data between 2021-01-10 and 2025-02-28 (yyyy-mm-dd), the query would be as follows:
SELECT * FROM [SalesInfo] WHERE [DateOfPurchase] BETWEEN '2021-01-10' AND '2025-02-28' --Ensure the date format matches the column data type (YYYY-MM-DD is recommended).
Pay attention to the Result set, you can see that both the dates are included.
It was pretty simple isn’t it. Now let’s see how stuff works if the date column is of type Datetime/Datetime2.
Make a note of the underlined datetime in red:
Execute the same query for ‘SalesData’ table and let’s compare the result sets:
Important to realize here is the underlined datetime value has not appeared in the result set though we said that BETWEEN operator is inclusive. Well, the catch here is when you specify the only date for a column of having data type Datetime/Datetime2 it simply considers midnight (12 am).
In other words SQL Engine interprets ‘2021-01-10‘ as ‘2021-01-10 00:00:00.000’ and ‘2025-02-28’ as ‘2025-02-28 00:00:00.000′. Because ‘2025-02-28 21:26:54.000’ is greater than ‘2025-02-28 00:00:00.000’, it is not included. Now you know why SQL omitted the underlined date in the above example.
SELECT Data Between Two Dates using Arithmetic Operators Greater Than (>) and Less Than (<)
These operators allows the flexibility to include or exclude the start and end dates by specifying equal operator along with them. That means if you use >= and <= it will work just like BETWEEN operator. Again if you are dealing with Datetime/Datetime2 data type, the same concept of midnight applies as described above.
Let’s consider the SalesInfo table where the data type for the date column is Date.
SELECT * FROM [SalesInfo] WHERE [DateOfPurchase] >= '2021-01-10' AND [DateOfPurchase] <= '2025-02-28'
The result set shows that both the dates are included:
As mentioned earlier it has the flexibility to exclude any of the dates. In the below example the end date is excluded by mentioning (<) operator.
SELECT * FROM [SalesInfo] WHERE [DateOfPurchase] >= '2021-01-10' AND [DateOfPurchase] < '2025-02-28'
Conclusion:
Selecting SQL Server data between two dates is a fundamental but powerful task. Whether you’re using BETWEEN, >= AND <, the key is understanding how SQL Server handles date and time. With these techniques, you can write efficient and accurate queries to get the data you need.
You may also want to check:
How To Rename SQL Server Database
List All Database Users With Permissions
Could not drop login as the user is currently logged in
List All Permissions For A User In All Or Selective Databases
