Best, Most Efficient DATETIME filtering

For SQL Server, both of the predicates

WHERE <DATETIME_Column_Name> >= '2026-02-17 00:00:00.000'
WHERE <DATETIME_Column_Name> >= '2/17/2026'

are logically equivalent if the second literal is interpreted as midnight on 2026‑02‑17. From a performance perspective, the key points are:

1. SQL Server stores datetime as a numeric value, and any constant date literal is converted to that internal value before the query runs. Once compiled, the plan uses a parameter/constant of type datetime, not the original string, so there is no meaningful runtime performance difference between the two literal formats.
2. The real performance driver is whether the predicate is sargable (can use an index seek and segment/rowgroup elimination) and whether an index exists on the column. A simple range predicate like:

WHERE <DATETIME_Column_Name> >= @StartDate
  AND <DATETIME_Column_Name> <  @EndDate

against an indexed datetime column is optimal. Avoid wrapping the column in functions (for example WHERE CONVERT(date, <DATETIME_Column_Name>) = '2026-02-17'), because that prevents efficient index usage.

3. Use an unambiguous, ISO-style format for literals to avoid misinterpretation by DATEFORMAT/language settings. The context explicitly recommends YYYYMMDD without delimiters as always interpreted correctly by SQL Server date-related data types, and notes that YYYY-MM-DD is reliably understood by the newer types but not by older ones like datetime when DATEFORMAT changes.

So, for both correctness and maintainability, a recommended pattern is:

DECLARE @StartDate datetime = '20260217';       -- 2026-02-17 00:00:00.000
DECLARE @EndDate   datetime = '20260218';       -- 2026-02-18 00:00:00.000

SELECT ...
FROM   YourTable
WHERE  <DATETIME_Column_Name> >= @StartDate
   AND <DATETIME_Column_Name> <  @EndDate;

This is efficient (index-friendly) and avoids regional-format ambiguity. The choice between including an explicit time portion ('2026-02-17 00:00:00.000') or relying on the default midnight for '20260217' does not affect performance; it is a clarity and safety issue, where the unambiguous ISO date literal is preferred.

References:

• CHANGING DATE FORMAT OF SQL SERVER DATABASE - Microsoft Q&A
• Columnstore indexes - query performance
• Rules for entering search values (Visual Database Tools)
• WHERE Clause