SQL Server Filter By Date

by

In this comprehensive article, I’ll walk you through everything you need to know about filtering data by date in SQL Server, from basic techniques to advanced strategies.

SQL Server Filter By Date

Before diving into specific approaches, it’s crucial to understand how SQL Server stores and processes date and time data. SQL Server offers several date-related data types, each with specific use cases:

  • date: Stores date only (no time component)
  • time: Stores time only (no date component)
  • datetime: Stores both date and time (accuracy to 3.33 milliseconds)
  • datetime2: Enhanced version with greater date range and time precision
  • datetimeoffset: Includes time zone awareness

Choosing the right data type for your specific needs is the first step toward effective date filtering.

Basic Date Filtering Approaches

Let’s explore the fundamental methods for filtering by date in SQL Server.

Approach 1: Using Simple Comparison Operators

The most straightforward approach to date filtering uses basic comparison operators:

Example

The below query will retrieve all sales placed on May 4, 2025.

SELECT *
FROM Sales
WHERE sale_date = '2025-05-04';

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

SQL Server Filter By Date

Similarly, you can execute the following query to retrieve all sales after May 4, 2025.

SELECT *
FROM Sales
WHERE sale_date > '2025-05-04';

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

sql filter by date greater than

Approach 2: Using the BETWEEN Operator

The BETWEEN operator in SQL Server is simple for filtering records within a date range:

-- Retrieve all orders placed in Q1 2025
SELECT *
FROM Sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31';

You can check out the screenshot below, which shows that we got the expected output.

sql select date older than

Approach 3: Using Date Functions

SQL Server provides numerous date functions that allow for more granular filtering:

Example-1

We can use the following query to find all sales from the current month.

SELECT *
FROM Sales
WHERE MONTH(sale_date) = MONTH(GETDATE())
AND YEAR(sale_date) = YEAR(GETDATE());

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

sql filter by date between

Example-2

The query below will help you retrieve last year’s sales list.

-- Find all sales from last year
SELECT *
FROM Sales
WHERE YEAR(sale_date) = YEAR(GETDATE()) - 1;

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

sql filter by date with time

Approach 3: Using the DATEADD Function

Example-1

The query below will give you the sales list for the last 30 days.

SELECT *
FROM Sales
WHERE sale_date >= DATEADD(day, -30, GETDATE());

I got the expected output as per the screenshot below.

sql filter by date column

Example-2

The below query will get the sales from the current quarter.

SELECT *
FROM Sales
WHERE sale_date >= DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0)
AND sale_date < DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) + 1, 0);

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

sql where date = specific date

Best Practices

Here are some best practices to follow:

1. Use Appropriate Data Types

Match your data type to your actual needs:

RequirementRecommended Type
Date onlydate
Date with timedatetime2
Time zone supportdatetimeoffset

2. Handling String-to-Date Conversions

Always use unambiguous date formats to avoid regional setting issues:

-- Recommended approach using ISO format (YYYY-MM-DD)
SELECT *
FROM Sales
WHERE Sale_Date = '2025-05-04';
sql filter by date

We can also use the safer approach i.e using explicit conversion.

-- Safer approach using explicit conversion
SELECT *
FROM Sales
WHERE sale_date = CONVERT(date, '2025-05-04', 120);

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

sql server filter by date range

3. Filter by Date Range Efficiently

When working with datetime columns and you only care about the date portion:

We can use the below query to filter for a specific date when column contains time component.

SELECT *
FROM Sales
WHERE sale_date >= '2025-01-15' AND sale_date < '2025-05-16';

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

sql filter date last 30 days

Handling Common Date Filtering Issues

Let’s address some typical challenges you might encounter.

Working with Date-Only Comparisons on DateTime Columns

If your column stores datetime but you want to filter by date only, you can use the below query.

SELECT *
FROM Sales
WHERE CAST(sale_date AS date) = '2025-05-04';

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

sql query filter by date range

Conclusion

Understanding date filtering in SQL Server is essential for a database developer. By understanding the approaches mentioned in this article and following best practices, you can easily implement this in your application.

You may also like following the articles below