Compare dates in T-SQL, ignoring the time part

Techniques to handle date comparisons

Handling dates in SQL queries often require a mix of precision and performance. Here are some strategies that can help manage and compare dates more effectively.

Pure date data types: Less is More

If only the date part is relevant for your table, consider using DATE instead of DATETIME. It saves you the trouble of peeling off the time bit in every comparison:

CREATE TABLE YourTable (
    YourDateColumn DATE,
    -- other columns
);


Pre-computed columns: Pre-emptive Strike

Creating views or indexed computed columns with pre-computed date comparisons can amp up your query performance:

CREATE VIEW YourView AS
SELECT *,
       CAST(YourDateTimeColumn AS DATE) as YourDateColumnOnly
FROM YourTable;


DATEDIFF: Not a fan of boundaries

DATEDIFF calculates the difference between two dates based on a specified interval but it's more into crossing "boundaries" than elapsed time:

SELECT DATEDIFF(day, '2021-06-20', '2021-06-21') AS DaysDiff; -- Returns 1, but it's not ghosting you.


Bear in mind that too many DATEDIFF calculations can put a dent in your query performance.

CONVERT: Your Formatting Connoisseur

CONVERT can be a neat trick to pull out for consistent formats. Using style code 112 treats just the date part:

SELECT *
FROM YourTable
WHERE CONVERT(VARCHAR, YourDateTimeColumn, 112) = CONVERT(VARCHAR, 'TargetDate', 112);


Make sure to test performance in your specific environment as efficiency can vary.

Effective practices and clever workarounds

Knowing how to dodge potential pitfalls and use clever tricks will lead to more efficient and readable queries.

Get with the Indexing program

If your date columns find themselves often in WHERE clauses or JOIN conditions, make them feel more special – make them indexed columns. Your search performance will thank you.

Trade-offs: Keep 'em in check

Converting dates into VARCHAR for comparisons can be akin to duct-taping a quick fix – it works but has trade-offs. Such conversions can potentially disrupt index usage and are generally not the quickest kids on the block.

A Purpose-built model

If your data model aligns with the needs of efficient date comparison, you've hit the jackpot. Separating date and time where it makes sense could be a lifestyle in columns or tables.

Test like your life depends on it

Always have a testing protocol in place for your date comparison methods. Different scenarios and environmental factors can give varying results – remember, your data is unique!