How to compare datetime with only date in SQL Server

SARGable vs. Non-SARGable: The Efficiency Choice

A SARGable approach raises the efficiency of indexing and querying. Using functions like CONVERT() on datetime columns turn queries non-SARGable. Instead, use a range comparison with >= and < :

SELECT * FROM YourTable 
WHERE DateTimeColumn >= 'YYYYMMDD' AND DateTimeColumn < 'YYYYMMDD';


-- Yes, it's like asking if your salary is >= lowest desired and < ridiculously high. We keep our hopes high!

The Full 24: Range comparison Strategy

In SQL Server, comparing datetime values is more than just the date. It's about the ensuing 24 hours, down to the millisecond. To capture an entire day, use range comparisons:

SELECT * FROM YourTable 
WHERE DateTimeColumn >= 'YYYY-MM-DD 00:00:00' AND DateTimeColumn < 'YYYY-MM-DD 23:59:59.997';


-- Yes, in SQL Server land, a day ends at 23:59:59.997. Not at midnight, apparently!

The Old Chap's Rule: Prior SQL Server 2008

Before 2008, SQL Server had no DATE. So, the old chap had to convert datetime to varchar and back:

SELECT * FROM YourTable 
WHERE CONVERT(datetime, CONVERT(varchar, DateTimeColumn, 101)) = 'MM/DD/YYYY';


-- Yes, the SQL equivalent of taking a car apart and putting it back together again just to paint it!

The BETWEEN Pitfall

A datetime comparison using BETWEEN can be misleading due to the inclusion of the end value’s time stamp. Handcraft your date ranges to avoid confounding results:

-- Incorrect: Might include times from the next day 
SELECT * FROM YourTable 
WHERE DateTimeColumn BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';

-- Correct: Make bounds obvious
SELECT * FROM YourTable 
WHERE DateTimeColumn >= 'YYYY-MM-DD' AND DateTimeColumn < 'YYYY-MM-DD 23:59:59.997';


-- It's BETWEEN rock and a hard place, but you've got this!

The Here and Now: Getting Current Date

GETDATE() is a built-in function that gives you the current system date and time. Remember to cast it to a DATE for a comparison for today's date:

SELECT * FROM YourTable 
WHERE CAST(DateTimeColumn AS DATE) = CAST(GETDATE() AS DATE);


-- Yes, we're time travelers checking if DateTimeColumn exists any time during today!