Tuesday, May 14, 2024

How to get rows between two dates in Microsoft SQL Server? MSSQL Example

Problem: You want all records between two dates e.g. start_date and end_date. For example, you have a Course table, which contains course_name, price and start_date. If want all the course which are starting next month, how will you do that?

Solution : You can get the records between two dates in SQL SERVER by using dates in WHERE clause, for example, below query will return all the courses for which start date is greater than 2025/01/01 and less than 2025/01/31

Before we run query, let's first create the table and insert data. I am using T-SQL to write an SQL script which can create a temporary table (any table which name start with #) so that it will automatically be removed once we log off the SQL Server Management Studio session.

IF OBJECT_ID( 'tempdb..#Course' ) IS NOT NULL


CREATE TABLE #Course (course_name varchar(10), price numeric (31,6), start_date datetime);

INSERT INTO #Course VALUES ('Java', 100, '2025-10-09');

INSERT INTO #Course VALUES ('MySQL', 60, '2025-10-19');

INSERT INTO #Course VALUES ('SQL SERVER', 170, '2025-11-09');

INSERT INTO #Course VALUES ('PostgreSQL', 190, '2025-11-29');

INSERT INTO #Course VALUES ('Oracle', 110, '2025-10-18');

INSERT INTO #Course VALUES ('SQLLite', 200, '2025-10-19');

Once we created table, I have inserted 6 rows into Course table with different start_date which we will use for query. 

Now, let's find out all the courses which are starting this month :

SELECT * FROM #Course WHERE start_date >= '2025-10-01' and start_date <='2025-10-31'

Here is the output when you run this query in our #Course table which is a temp table 

| course_name | price | start_date           |
| Java        | 100.00| 2025-10-09 00:00:00  |
| MySQL       | 60.00 | 2025-10-19 00:00:00  |
| Oracle      | 110.00| 2025-10-18 00:00:00  |
| SQLLite     | 200.00| 2025-10-19 00:00:00  |

Now, let's one for query to find all the courses which are starting between  '2025-10-01' and '2025-10-31' dates

SELECT * FROM #Course WHERE start_date between '2025-10-01' and '2025-10-31'

Here is the output

| `course_name` | `price` | `start_date`          |
| Java          | 100.00  | 2025-10-09 00:00:00   |
| MySQL         | 60.00   | 2025-10-19 00:00:00   |
| Oracle        | 110.00  | 2025-10-18 00:00:00   |
| SQLLite       | 200.00  | 2025-10-19 00:00:00   |

When you store just date e.g. '2025-10-08' in a DATETIME field then SQL Server stores "00:00:00.000" in time field, as seen in output.  By the way, there are many date and time data types in SQL Server, here is quick summary of them:

SQL Server date and time data types

That's all about how to get rows between two dates in MSSQL. This is simple example of filtering date in SQL. Unlike numbers, date are bit tricky because both date and time element are in play and also there are different data types which you can use to store date related data. 

Knowing all MSSLQ date and time data types and when to use them goes a long way in becoming an efficient developer who can work with SQL Server database easily. 

Other SQL Tutorials and Articles you may like


  1. How about using BETWEEN keyword to find records in a date range?

    1. You can use BETWEEN, no problem, just beware how you compare DATETIME with String value because they are cast to DATETIME value using database settings which can result in unexpected way.

  2. I highly recommend adding 23:59 to the end date when using a datetime field. Not needed if it was a date datatype field.