Guides › Datetime to date

Convert datetime to date in SQL Server without breaking the index

You want “on this day”, but the column has a time. Cast the value you compare against, not the whole column, if you can help it.

Drop the time on a value

SELECT CAST(GETDATE() AS date) AS today,
       CONVERT(date, GETDATE()) AS also_today;

Either is fine. CAST is easier to read.

Filter a datetime column by a day

DECLARE @day date = '2026-10-30';

SELECT order_id, order_date
FROM Orders
WHERE order_date >= @day
  AND order_date <  DATEADD(day, 1, @day);

The column stays bare. SQL Server can use an index on order_date.

Wrapping the column

WHERE CAST(order_date AS date) = @day is easy and often “fine” on a small table. On a large one it can force a scan. Same rows, slower plan.

Showing a date in the result

SELECT order_id, CAST(order_date AS date) AS order_day, total_amount
FROM Orders;

Casting in SELECT is about the header you send. That does not hurt the filter.

Try it on Harbour & Co

James Rowe (Sales Manager) needs this now: “Need last week’s orders over £50 for the 10am. Don’t include today. Newest first.” Write the query, run it, send the results. Free.

Take the ticket →

Learn it on a real ticket

Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.

Clock in free See Monday’s tickets