SQL Server Convert Datetime to Date: CAST, CONVERT, WHERE Clause and Errors

A report filtered on PlacedAt = '2026-07-04' returned one order. The day had four. Blame the time part.

Quick answer (SQL Server convert datetime to date): CAST(PlacedAt AS date) or CONVERT(date, PlacedAt). Both drop the time and return a real date type. For today, wrap GETDATE():

SET NOCOUNT ON;
SELECT GETDATE()                    AS NowDatetime,
       CAST(GETDATE() AS date)      AS CastToday,
       CONVERT(date, SYSDATETIME()) AS ConvertToday;

Output:

NowDatetime             CastToday        ConvertToday    
----------------------- ---------------- ----------------
2026-10-30 11:50:29.670       2026-10-30       2026-10-30

What You Need Before You Start

Tested on SQL Server 2025 (17.0.1000.7) Developer edition, sqlcmd in VS Code, language us_english. Any version from 2008 on has the date type; DATETRUNC needs SQL Server 2022.

Here’s the orders table we’ll reuse. Order 4 sits at 23:59:59.997 because that’s where date filters break. Swap GETDATE() for CURRENT_TIMESTAMP anywhere below.

CREATE TABLE dbo.Orders (
    OrderID  int          PRIMARY KEY,
    Customer nvarchar(14) NOT NULL,
    Amount   decimal(8,2) NOT NULL,
    PlacedAt datetime     NOT NULL
);
INSERT INTO dbo.Orders VALUES
(1, N'Hannah Brooks',  129.99, '2026-07-04 00:00:00'),
(2, N'Tyler Reed',      54.10, '2026-07-04 09:15:42.250'),
(3, N'Grace Miller',   310.00, '2026-07-04 18:40:05'),
(4, N'Marcus Bennett',  22.75, '2026-07-04 23:59:59.997'),
(5, N'Olivia Hayes',    88.40, '2026-07-05 06:05:00');

Step 1: CAST or CONVERT the Datetime to Date (Not to a String)

You’ll see three functions suggested for this. Only two return a date:

SET NOCOUNT ON;
SELECT OrderID,
       CAST(PlacedAt AS date)              AS CastDate,
       CONVERT(varchar(10), PlacedAt, 101) AS UsString,
       DATETRUNC(day, PlacedAt)            AS Truncated
FROM dbo.Orders
WHERE OrderID IN (2, 4);

DECLARE @now datetime = GETDATE();
SELECT CAST(SQL_VARIANT_PROPERTY(CAST(@now AS date), 'BaseType')
            AS varchar(8)) AS CastType,
       CAST(SQL_VARIANT_PROPERTY(CONVERT(varchar(10), @now, 101),
            'BaseType') AS varchar(8)) AS StyleType,
       CAST(SQL_VARIANT_PROPERTY(DATETRUNC(day, @now), 'BaseType')
            AS varchar(8)) AS TruncType;

Output:

OrderID     CastDate         UsString   Truncated              
----------- ---------------- ---------- -----------------------
          2       2026-07-04 07/04/2026 2026-07-04 00:00:00.000
          4       2026-07-04 07/04/2026 2026-07-04 00:00:00.000
CastType StyleType TruncType
-------- --------- ---------
date     varchar   datetime

CastType says date, StyleType says varchar, TruncType says datetime. So CONVERT(varchar(10), x, 101) gives you 07/04/2026 as text: fine for display, wrong for sorting and date math.

Need text on purpose? The datetime to string style codes and SQL Server date format options cover that side.

Step 2: Convert datetime2 and datetimeoffset to Date

Your column might not be plain datetime. Two inputs surprised me:

SET NOCOUNT ON;
DECLARE @dt2 datetime2(7) = '2026-07-04 23:59:59.9999999';
DECLARE @dto datetimeoffset(0) = '2026-07-04 21:30:00 -07:00';

SELECT CAST(@dt2 AS date)                   AS FromDatetime2,
       CAST(CAST(@dt2 AS datetime) AS date) AS ViaDatetime,
       CAST(@dto AS date)                   AS FromOffset,
       CAST(@dto AT TIME ZONE 'UTC' AS date) AS UtcDate;

Output:

FromDatetime2    ViaDatetime      FromOffset       UtcDate         
---------------- ---------------- ---------------- ----------------
      2026-07-04       2026-07-05       2026-07-04       2026-07-05

Casting datetime2 straight to date keeps July 4. Pass it through datetime first and it rounds up to July 5, since datetime only stores about 3 ms. That precision gap is the core of datetime vs datetime2.

A datetimeoffset cast keeps the local date (July 4). If you want the UTC calendar day, convert with AT TIME ZONE 'UTC' first.

Step 3: Convert Datetime to Date in a WHERE Clause

Same five orders. I counted July 4 rows four ways:

SET NOCOUNT ON;
SELECT
 (SELECT COUNT(*) FROM dbo.Orders
   WHERE PlacedAt = '2026-07-04') AS EqualsDate,
 (SELECT COUNT(*) FROM dbo.Orders
   WHERE CAST(PlacedAt AS date) = '2026-07-04') AS CastDate,
 (SELECT COUNT(*) FROM dbo.Orders
   WHERE PlacedAt >= '2026-07-04'
     AND PlacedAt <  '2026-07-05') AS HalfOpen,
 (SELECT COUNT(*) FROM dbo.Orders
   WHERE PlacedAt BETWEEN '2026-07-04'
     AND '2026-07-04 23:59:59') AS Between59;

Output:

EqualsDate  CastDate    HalfOpen    Between59  
----------- ----------- ----------- -----------
          1           4           4           3

The literal ‘2026-07-04’ becomes midnight, so = only matches order 1. BETWEEN up to 23:59:59 misses order 4 at .997.

Use CAST(PlacedAt AS date) = '2026-07-04' or the half-open >= day AND < next day range. Both keep everything from midnight up to, but not including, the next midnight.

If your literals come in as mm/dd/yyyy text, read convert a string to date first.

Does CAST in a WHERE Clause Stop SQL Server From Using the Index?

No. I tested it on a 105,000-row log table with an index on PlacedAt, using SET STATISTICS IO ON (I cut each line after logical reads):

SET NOCOUNT ON;
SET STATISTICS IO ON;
SELECT COUNT(*) AS CastRows FROM dbo.OrderLog
WHERE CAST(PlacedAt AS date) = '2026-07-04';

SELECT COUNT(*) AS RangeRows FROM dbo.OrderLog
WHERE PlacedAt >= '2026-07-04' AND PlacedAt < '2026-07-05';

SELECT COUNT(*) AS StringRows FROM dbo.OrderLog
WHERE CONVERT(char(8), PlacedAt, 112) = '20260704';

Output:

CastRows   
-----------
        288
Table 'OrderLog'. Scan count 1, logical reads 3, ...
RangeRows  
-----------
        288
Table 'OrderLog'. Scan count 1, logical reads 2, ...
StringRows 
-----------
        288
Table 'OrderLog'. Scan count 1, logical reads 236, ...

CAST to date read 3 pages, the range read 2, and the string version read 236. SQL Server turns a date cast into a range seek; a style-112 string can’t use the index, so it scanned.

Step 4: GROUP BY Date for Daily Totals

SET NOCOUNT ON;
SELECT CAST(PlacedAt AS date) AS OrderDay,
       COUNT(*)               AS Orders,
       CAST(SUM(Amount) AS decimal(8,2)) AS Sales
FROM dbo.Orders
GROUP BY CAST(PlacedAt AS date)
ORDER BY OrderDay;

Output:

OrderDay         Orders      Sales     
---------------- ----------- ----------
      2026-07-04           4     516.84
      2026-07-05           1      88.40

Group by the same expression you select: July 4 shows four orders for $516.84. It works with joins too, as in this COUNT with JOIN and GROUP BY example.

Step 5: Truncate Datetime to the Hour or Drop Milliseconds

SET NOCOUNT ON;
SELECT OrderID,
       DATETRUNC(hour, PlacedAt)      AS HourStart,
       CAST(PlacedAt AS datetime2(0)) AS NoMilliseconds
FROM dbo.Orders
WHERE OrderID IN (2, 4);

Output:

OrderID     HourStart               NoMilliseconds                        
----------- ----------------------- --------------------------------------
          2 2026-07-04 09:00:00.000                    2026-07-04 09:15:42
          4 2026-07-04 23:00:00.000                    2026-07-05 00:00:00

DATETRUNC(hour, PlacedAt) keeps the datetime type. CAST(PlacedAt AS datetime2(0)) removes milliseconds but rounds, and order 4 jumped to midnight on July 5. If rounding isn’t allowed, use DATETRUNC(second, PlacedAt) instead.

Other date helpers are in the SQL Server datetime functions list.

Datetime to Date Cheat Sheet

You want	Use	Returns
Date only	CAST(x AS date) / CONVERT(date, x)	date
Today	CAST(GETDATE() AS date)	date
Midnight, same type	DATETRUNC(day, x) (2022+)	input type
One day in WHERE	x >= @d AND x < DATEADD(day, 1, @d)	index seek
mm/dd/yyyy text	CONVERT(varchar(10), x, 101)	varchar
UTC day	CAST(x AT TIME ZONE 'UTC' AS date)	date

Common Mistakes to Avoid: Datetime to Date Errors (Msg 241, 242, 206, 9810)

I hit each one on purpose. EXEC inside TRY…CATCH catches even the compile error (206):

SET NOCOUNT ON;
SET LANGUAGE us_english;
BEGIN TRY EXEC (N'DECLARE @d date = CAST(''07/32/2026'' AS date);'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

BEGIN TRY EXEC (N'DECLARE @d datetime = ''2026-02-30'';'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

BEGIN TRY EXEC (N'SELECT CAST(GETDATE() AS date) + 1;'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

BEGIN TRY EXEC (N'DECLARE @d date = DATEADD(hour, 1, CAST(GETDATE() AS date));'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

Output:

Changed language setting to us_english.
Msg 241: Conversion failed when converting date and/or time from character string.
Msg 242: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Msg 206: Operand type clash: date is incompatible with int
Msg 9810: The datepart hour is not supported by date function dateadd for data type date.

• Msg 241: not a valid date string. See fixing Msg 241.
• Msg 242: no such day. Same number as date to datetime out-of-range errors.
• Msg 206: date + 1 only works on datetime. Use DATEADD(day, 1, d).
• Msg 9810: a date has no hours. Add hours before you cast, not after.

Next Steps After You Convert Datetime to Date

Moving the same report to PostgreSQL? There it’s timestamp::date; see PostgreSQL cast timestamp to date.

If it still fails:

• Wrong count for a day? Look for = on a datetime column.
• Off by one day? Something rounded, or a time zone wasn’t converted.
• Slow filter? You’re comparing strings.

Related reading for date work:

• Join on the latest date per customer
• Get the month name from a date
• PostgreSQL date_trunc

Frequently Asked Questions