SQL Server Check If Date Is Today

One of the most common requirements is checking if a date field contains today’s date. In this comprehensive article, I’ll walk you through multiple methods to check if a date is today in SQL Server, sharing the techniques.

SQL Server Check If Date Is Today

Before diving into specific solutions, it’s essential to understand why comparing dates in SQL Server requires special consideration.

When working with datetime values in SQL Server, you’re typically dealing with both date and time components. This means that a simple equality check between a datetime column and today’s date can be problematic because the time portions will likely differ.

Let me show you the most effective approaches to handle this common requirement.

Approach 1: Using CONVERT to Remove Time Component

The most reliable approach is to convert both the column value and the current date to remove the time component:

SELECT * 
FROM Orders
WHERE CONVERT(date, OrderDate) = CONVERT(date, GETDATE());

This method works by:

• Converting the OrderDate column to a date data type, which eliminates the time portion
• Converting the result of GETDATE() to a date data type to remove the time similarly
• Comparing these date-only values for equality

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

Approach 2: Using DATEADD and DATEDIFF for Range Comparison

Another practical approach is to check if the date falls within the range of the current day:

SELECT * 
FROM Orders
WHERE OrderDate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
  AND OrderDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 1, 0);

This method:

• Uses DATEDIFF(day, 0, GETDATE()) to determine the number of days between the SQL Server base date (January 1, 1900) and today
• Uses DATEADD to add that number of days to the base date, giving us today at midnight (00:00:00). Adds one more day to get tomorrow at midnight
• Checks if the OrderDate is greater than or equal to today at midnight and less than tomorrow at midnight

Approach 3: Using the CAST function

A slightly more concise version uses CAST instead of CONVERT:

SELECT * 
FROM Orders
WHERE CAST(OrderDate AS date) = CAST(GETDATE() AS date);

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

Functionally, this is equivalent to Method 1. The choice between CAST and CONVERT It is essentially a matter of personal preference, though CONVERT it offers more formatting options for more complex scenarios.

Approach 4: Using DATEPART Functions

Use the DATEPART function to compare individual date components:

SELECT * 
FROM Orders
WHERE DATEPART(year, OrderDate) = DATEPART(year, GETDATE())
  AND DATEPART(month, OrderDate) = DATEPART(month, GETDATE())
  AND DATEPART(day, OrderDate) = DATEPART(day, GETDATE());

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

While this works, I generally avoid this method in new development because:

1. It requires more CPU processing
2. It can prevent efficient use of indexes
3. The syntax is more verbose

However, I include it because you may encounter this pattern in existing code, and understanding it is valuable for maintenance purposes.

Approach 5: Using DATE Data Type (SQL Server 2008 and Later)

If you’re working with SQL Server 2008 or later, you can use the DATE data type directly:

We can execute the query below if your column is already of the DATE data type.

SELECT * 
FROM Orders
WHERE Order_Date = CAST(GETDATE() AS date);

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

We can execute the query below if your column is already of the datetime data type.

SELECT * 
FROM Orders
WHERE CAST(Order_Date AS date) = CAST(GETDATE() AS date);

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

Approach 6: Using BETWEEN with Date Boundaries

Some developers prefer using the BETWEEN operator for date ranges:

SELECT * 
FROM Orders
WHERE Order_Date BETWEEN 
    CAST(GETDATE() AS date) AND 
    DATEADD(millisecond, -3, DATEADD(day, 1, CAST(GETDATE() AS datetime)));

This approach:

• Uses CAST(GETDATE() AS date) to get today at midnight
• Adds one day to get tomorrow at midnight
• Subtracts 3 milliseconds to get 23:59:59.997 today (just before midnight)
• Checks if OrderDate falls between these boundaries

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

Approach 7: Creating a Reusable Function

We can create a reusable function using the below query.

CREATE FUNCTION dbo.IsToday(@Date datetime)
RETURNS bit
AS
BEGIN
    RETURN CASE WHEN CONVERT(date, @Date) = CONVERT(date, GETDATE()) THEN 1 ELSE 0 END
END
GO

After executing the above query, the function was created successfully, as shown in the screenshot below.

Now, we can call the above function to check if the date is today using the following query.

-- Usage
SELECT * 
FROM Orders
WHERE dbo.IsToday(Order_Date) = 1;

The function approach:

1. Provides consistency across the application
2. Makes queries more readable
3. Centralizes the logic so it can be updated in one place if needed

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

Performance

When dealing with large tables, performance becomes critical. Here are key performance considerations:

Indexing Strategy

If you frequently filter by today’s date, consider an appropriate indexing strategy:

-- Create an index on the date column
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);

However, be aware that using functions on indexed columns can prevent the SQL Server query optimizer from using the index effectively.

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

Method Performance Comparison

Here’s how the different methods typically compare:

Conclusion

Below are the best practices:

• For most situations, use either:WHERE CONVERT(date, DateColumn) = CONVERT(date, GETDATE()) orWHERE DateColumn >= CAST(GETDATE() AS date) AND DateColumn < DATEADD(day, 1, CAST(GETDATE() AS date))
• For performance-critical systems with millions of rows, prefer the range-based approach with proper indexing.
• For new database designs, consider using the DATE data type for columns that don’t need time components.
• For applications with many “is today” checks, create a reusable function to ensure consistency.

By understanding the approaches explained in this article for checking if a date is today in SQL Server, you can achieve this requirement.

You may also like the following articles.

• How to Calculate Age from Date of Birth in SQL Server
• The datepart millisecond is not supported by the date function dateadd for the data type date.
• SQL Server Short Date
• SQL Server First Date Of the Month
• Add Month To Date SQL Server

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.