SQL Server Date Between

by

In this article, I’ll walk you through everything you need to know about working with date ranges in SQL Server, with a specific focus on the BETWEEN operator.

SQL Server Date Between

Before diving into specific techniques, it’s essential to understand how SQL Server handles dates. SQL Server stores dates in a standardized internal format, but how we interact with those dates in our queries can significantly impact both the accuracy of our results and query performance.

Let’s explore the most common method for filtering date ranges: the BETWEEN operator.

The BETWEEN operator provides a straightforward way to select values within a specified range. Its syntax is clean and intuitive:

Syntax

SELECT column_name(s)
FROM table_name
WHERE date_column BETWEEN start_date AND end_date;

This operator includes both the start and end dates, which means records exactly matching either boundary will be included in your results.

Example

Let’s say we’re analyzing sales data for Q1 2025. The below query returns all sales transactions during the first quarter of 2025.

SELECT 
    sale_date, 
    sale_amount, 
    TotalSales
FROM 
    Sales
WHERE 
    sale_date BETWEEN '2025-01-01' AND '2025-03-31';

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Date Between

Challenges of BETWEEN with Dates

While BETWEEN might seem straightforward, it comes with several considerations that can trip up even experienced developers:

1. Time Component Challenges

When using BETWEEN with datetime columns, the time component can cause unexpected results. For example:

-- This might not include orders placed on March 31st after midnight
SELECT * FROM Sales 
WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31';

If a customer placed a sales order at 3:45 PM on March 31st, it would be included. However, SQL experts recommend avoiding BETWEEN for datetime ranges due to these time component complications.

Better Alternatives to BETWEEN for Date Ranges

In my experience optimizing thousands of queries, I’ve found that explicit comparison operators often provide more clarity and control than BETWEEN.

Using >= and < for Date Ranges

SQL experts recommend using >= and < operators instead of BETWEEN for date ranges:

SELECT * FROM Sales 
WHERE sale_date >= '2025-01-01' 
  AND sale_date < '2025-04-01';

This approach:

  • Defines the range boundaries
  • Avoids time component issues
  • Improves query readability
  • Often performs better in execution plans

After executing the above query, I got the expected output as shown in the screenshot below.

sql server date between two dates

Optimizing Date Range Queries for Performance

After optimizing hundreds of database systems, I’ve found these strategies particularly effective for date range query performance:

1. Ensure Proper Indexing

Create appropriate indexes on date columns that are frequently used in WHERE clauses:

CREATE INDEX IX_Sales_SaleDate ON Sales(Sale_Date);

2. Avoid Functions on Indexed Columns

Instead of:

WHERE YEAR(Sale_Date) = 2025

Use:

WHERE Sale_Date >= '2025-01-01' AND Sale_Date < '2026-01-01'

This enables SQL Server to utilize indexes efficiently.

Best Practices

Based on my experience working with enterprise databases, here are my top recommendations:

  • Be explicit about date boundaries: Use >= and < rather than BETWEEN
  • Standardize date formats: Use ISO format (YYYY-MM-DD) or YYYYMMDD
  • Include time components when needed: Be precise about time boundaries
  • Use parameters for flexibility: Avoid hardcoding dates in queries
  • Consider time zones: Use DATETIMEOFFSET when dealing with global data

Conclusion

Knowing the date range queries in SQL Server is essential. While the BETWEEN operator may seems intuitive, understanding its limitations and alternatives will help you write more robust queries as mentioned in this article.

You may also like the following articles