How to Truncate Date in SQL Server

How do I truncate the time portion of a datetime in SQL Server?

DATETRUNC (SQL Server 2022+) or CAST/CONVERT strips or rounds datetime values to the desired date part, removing the time component for cleaner grouping and comparison.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why truncate dates in SQL Server?

Truncating dates ensures consistent grouping, filtering, and joining by removing time fragments that cause duplicate buckets or missed matches.

What functions remove the time portion?

SQL Server 2022+ offers DATETRUNC().Earlier versions rely on CAST()/CONVERT() to the date data type or DATEADD/DATEDIFF tricks.

How does DATETRUNC work?

DATETRUNC(datepart, datetime_expression) returns the datetime rounded down to the specified datepart (year, quarter, month, week, day, hour, etc.).

Example: truncate OrderDate to month

SELECT DATETRUNC(month, order_date) AS order_month, COUNT(*) FROM Orders GROUP BY DATETRUNC(month, order_date);

How to truncate dates before SQL Server 2022?

Cast to date when only the calendar date matters: CAST(order_date AS date).For month starts: DATEADD(day, 1-DAY(order_date), CAST(order_date AS date)).

Best practices for date truncation

1) Prefer DATETRUNC when available—simpler and SARGable. 2) Create computed, indexed columns if filtering on truncated values frequently. 3) Store dates without time when time is irrelevant.

Common pitfalls and how to avoid them

Using non-SARGable expressions (e.g., CONVERT(varchar, order_date, 112)) forces scans; stick to date types or DATETRUNC for index usage.

Can I truncate to hours or minutes?

Yes—pass hour or minute as the datepart.Example: DATETRUNC(hour, created_at) buckets log data by hour.

Does DATETRUNC affect the original data?

No. It returns a computed value; source rows remain unchanged. Persist the result in a separate column if you need it stored.

Why How to Truncate Date in SQL Server is important

How to Truncate Date in SQL Server Example Usage


-- How many orders were placed each week?
SELECT DATETRUNC(week, order_date) AS order_week,
       COUNT(*)                    AS order_count
FROM   Orders
GROUP  BY DATETRUNC(week, order_date)
ORDER  BY order_week;

How to Truncate Date in SQL Server Syntax


-- General syntax (SQL Server 2022+)
DATETRUNC ( <datepart>, <datetime_expression> )

-- Supported dateparts: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond

-- Ecommerce examples
-- 1. Strip time from Orders.order_date
SELECT DATETRUNC(day, order_date) AS order_day FROM Orders;

-- 2. Monthly revenue
SELECT DATETRUNC(month, order_date)   AS order_month,
       SUM(total_amount)              AS monthly_sales
FROM   Orders
GROUP  BY DATETRUNC(month, order_date);

-- Pre-2022 alternative
SELECT CAST(order_date AS date) AS order_day FROM Orders;

Common Mistakes

Mistake: Converting datetime to varchar for comparison (<code>CONVERT(varchar, order_date, 112)</code>). Why wrong: breaks index seek, slows queries. Fix: use <code>DATETRUNC</code> or <code>CAST(... AS date)</code> so the predicate stays SARGable.
Mistake: Assuming CAST(datetime AS date) rounds to midnight of the same timezone when using datetimeoffset. Why wrong: implicit conversion may shift time zones. Fix: explicitly switch to <code>AT TIME ZONE</code> before casting, or use DATETRUNC on datetimeoffset.