SQL Server Date Compare

As a database developer working with SQL Server, I’ve encountered many scenarios where comparing dates has been crucial for accurate data analysis. Understanding how to compare dates properly in SQL Server is an essential skill.

I’ll cover everything you need to know about SQL Server date comparisons in this comprehensive article.

Table of Contents

Let us discuss all the possible methods to achieve this.

Method 1: Direct Comparison Using Comparison Operators

The most straightforward approach is using comparison operators like =, <, >, <=, >=, and <>. We can use the queries below for this purpose.

Example-1

The below query will check if a date is equal to another date.

SELECT * FROM Orders
WHERE Order_date = '2025-04-03';

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

Example-2

The below query will give you the orders placed before a specific date.

SELECT * FROM Orders
WHERE Order_date < '2025-04-03';

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

Example-3

The below query will list the orders placed on or after a specific date.

SELECT * FROM Orders
WHERE Order_date >= '2025-04-03';

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

While this method seems simple, it can cause issues if your Order_date column contains time components. For example, if an order was placed on ‘2025-04-03 14:30:00’, it wouldn’t match the first query.

Method 2: Using CAST or CONVERT Functions

To avoid time component issues, convert both sides of the comparison to date-only values:

Example-1: Using CAST()

Let us use the CAST function using the query below.

SELECT * FROM Orders
WHERE CAST(Order_date AS DATE) = '2025-04-03';

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

Example-2: Using CONVERT()

We can also use the CONVERT() function using the following query.

SELECT * FROM Orders
WHERE CONVERT(DATE, Order_date) = '2025-04-03';

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

This approach is more reliable for date-only comparisons.

Method 3: Using DATEADD and DATEDIFF for Date Range Queries

When dealing with date ranges, the DATEADD and DATEDIFF functions are useful

Example-1: Using DATEADD()

We can use the DATEADD() for this purpose using the query below. This query will find orders from the last 30 days.

SELECT * FROM Orders
WHERE Order_date >= DATEADD(DAY, -30, GETDATE());

After executing the above query, I got the expected output.

Example-2: Using DATEDIFF()

Using the query below, we can use the DATEDIFF().

SELECT * FROM Orders
WHERE DATEDIFF(MONTH, Order_date, GETDATE()) = 0;

I got the expected output as shown in the screenshot below.

Method 4: Using BETWEEN for Date Range

The BETWEEN operator provides a better way to find records within a specific date range.

SELECT * FROM Orders
WHERE Order_date BETWEEN '2025-04-01' AND '2025-06-08';

I got the expected output as shown in the screenshot below.

Method 5: Using EOMONTH

When dealing with month-end dates, the EOMONTH function can help you on this.

SELECT * FROM Orders
WHERE Order_date = EOMONTH(Order_date);

I got the expected output as shown below.

Date Comparison Best Practices for Performance

Here are some of the best practices

1. Use Appropriate Indexing

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

CREATE INDEX IX_Orders_Order_Date ON Orders(Order_date);

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

2. Avoid Functions on Indexed Columns

When possible, avoid applying functions to indexed columns in the WHERE clause:

-- Less efficient (doesn't use index effectively)
WHERE YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 11;

-- More efficient (can use index)
WHERE OrderDate >= '2025-03-01' AND OrderDate < '2023-04-01';

3. Use Parameterized Queries

Parameterize your date values as mentioned below.

-- Using the below parameterized query
DECLARE @StartDate DATE = '2025-04-01';
DECLARE @EndDate DATE = '2025-04-30';

SELECT * FROM Orders
WHERE Order_date BETWEEN @StartDate AND @EndDate;

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

Conclusion

Understanding date comparisons in SQL Server is essential for developers. Following the comparison methods explained in this article can help you to achieve this.

You may also like the following articles.

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.