SQL Server Date In Where Clause

by

In this comprehensive article, I’ll cover everything you need to know about using dates in SQL Server WHERE clauses, from basic syntax to advanced techniques.

SQL Server Date In Where Clause

The first challenge when working with dates in SQL Server is understanding the available data types.

Common Date Data Types in SQL Server

SQL Server offers several date-related data types:

  • DATE: Stores date only (no time component)
  • DATETIME: Stores both date and time (accuracy to 3.33 milliseconds)
  • DATETIME2: Enhanced version with larger date range and better precision
  • SMALLDATETIME: Compact storage with minute precision
  • DATETIMEOFFSET: Includes time zone awareness

The ISO-8601 Date Format

When working with date literals in SQL Server, using the ISO-8601 format with the following syntax is recommended.

WHERE OrderDate = '2023-05-26'  -- YYYY-MM-DD format

Let’s explore different ways to use the WHERE clause to filter by dates in SQL Server.

Case 1: Filtering for a Specific Date

You can use the following queries when filtering for records on a specific date.

The below query will get you the list of records on the sale_date ‘2025-03-23’.

Select * from sales
WHERE sale_Date = '2025-03-23'

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

sql server date in where clause

The second approach catches all records from the given date, regardless of the time component.

Case 2: Filtering for a Date Range

For date ranges, use comparison operators:

We can execute the query below to get a specified date range.

Select * from sales
WHERE sale_Date >= '2025-03-23' AND sale_Date < '2025-05-23'

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

date in where clause in sql server

Case 3: Filtering for Relative Dates

For dynamic date filtering:

If we wish to get the sales Orders from yesterday, we can execute the following query.

Select * from sales
WHERE sale_Date >= DATEADD(day, -1, CAST(GETDATE() AS date))
  AND sale_Date < CAST(GETDATE() AS date)

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

date in where clause sql server

Similarly, the following query can be used to retrieve the lists of Sales Orders from the last 30 days.

Select * from sales
WHERE sale_Date >= DATEADD(day, -30, GETDATE())

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

date in sql server where clause

Advanced Date Filtering Techniques

Working with DATETIME Columns When You Only Need the Date

When you have a DATETIME column (suppose the sale_Date column in my case), but only need to filter by the date component:

Select * from sales
WHERE CONVERT(date, sale_Date) = '2025-05-23'

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

sql server date format in where clause

Optimizing Date Range Queries

SQL Server can optimize date range queries when appropriately written:

For efficient index seeks, we can execute the following query.

Select * from sales
WHERE sale_Date BETWEEN '2025-05-23' AND '2025-05-23 23:59:59.997'

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

sql date in where clause

But for a more transparent and less prone-to-error approach, you can use the query below.

Select * from sales
WHERE sale_Date >= '2025-05-01' AND sale_Date < '2025-06-23'

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

date in where clause in sql

Working with Specific Time Components

Sometimes, you need to filter by specific times. For example, here, you wish to get the lists of sales orders between 9 and 16. You can use the query below for that purpose.

Select * from sales
WHERE DATEPART(HOUR, cast(cast(sale_Date as date) as datetime)) BETWEEN 9 AND 16

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

date in where clause sql

DateTime Functions That Work Well in WHERE Clauses

While applying functions to columns should generally be avoided, there are cases where SQL Server’s built-in functions can be helpful to:

  • DATEADD(): Add time intervals to dates
  • DATEDIFF(): Calculate differences between dates
  • EOMONTH(): Get the last day of the month

Conclusion

As explained above, following these guidelines and the information will help you write more reliable, performant SQL queries for date filtering with the WHERE clause. Below are the lists of the overall summary.

  1. Use ISO-8601 format (YYYY-MM-DD) for dates
  2. Avoid functions on columns in WHERE clauses
  3. Use half-open intervals (>= start AND < end) for date ranges
  4. Apply operations to parameters, not columns
  5. Create appropriate indexes on frequently filtered date columns
  6. Be careful with BETWEEN for datetime values due to precision issues
  7. Use parameterized queries in applications
  8. Consider partitioning for very large tables

You may also like the following articles