SQL Server 2022 introduced the DATETRUNC() function, which makes working with date and time values much easier. It trims (or “truncates”) a date/time value down to a specified part (like year, month, or week) while setting all smaller units to their starting value. This helps avoid the common hack of mixing DATEADD() and DATEDIFF() just to snap a timestamp to the beginning of a period.
In this article we’ll look at some examples that demonstrate how it works.
A Simple Example
Let’s use the DATETRUNC() function to truncate a date/time value to the day:
SELECT DATETRUNC(day, '2025-08-24 13:47:29.123');Result:
2025-08-24 00:00:00.0000000
That’s it. All we did was tell SQL Server something like “Take this value and cut it down to the start of the day”. The time portion is reset to midnight, because that’s the smallest unit inside a day.
Let’s try a different unit:
SELECT DATETRUNC(month, '2025-08-24 13:47:29.123');Result:
2025-08-01 00:00:00.0000000
This snaps the timestamp to the very start of the month, which is August 1st at midnight.
Supported Dateparts
DATETRUNC() works with a variety of date parts:
| Datepart | Abbreviations | Description / Notes |
|---|---|---|
year | yy, yyyy | Truncates to the start of the year |
quarter | qq, q | Truncates to the first day of the quarter |
month | mm, m | Truncates to the first day of the month |
dayofyear | dy, y | Same as day in that it truncates to the start of the day (removes the time component) |
day | dd, d | Same as dayofyear (truncates to the start of the day) |
week | wk, ww | Truncates to the first day of the week, as defined by the session’s @@DATEFIRST setting |
iso_week | isowk, isoww | Truncates to the first day of the ISO week (Monday, per ISO-8601) |
hour | hh | Truncates to the start of the hour |
minute | mi, n | Truncates to the start of the minute |
second | ss, s | Truncates to the start of the second |
millisecond | ms | Truncates to the millisecond precision (rounding down) |
microsecond | mcs | Truncates to the microsecond precision (rounding down) |
Each one zeroes out anything smaller than that unit.
Microsoft’s documentation explicitly states that:
The
weekday,timezoneoffset, andnanosecondT-SQL dateparts aren’t supported forDATETRUNC.
Comparing Dateparts
Let’s check each of the above dateparts with DATETRUNC():
DECLARE @dt DATETIME2(7) = '2025-08-24 13:47:29.1234567';
SELECT 'Year', DATETRUNC(year, @dt);
SELECT 'Quarter', DATETRUNC(quarter, @dt);
SELECT 'Month', DATETRUNC(month, @dt);
SELECT 'Week', DATETRUNC(week, @dt);
SELECT 'Iso_week', DATETRUNC(iso_week, @dt);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @dt);
SELECT 'Day', DATETRUNC(day, @dt);
SELECT 'Hour', DATETRUNC(hour, @dt);
SELECT 'Minute', DATETRUNC(minute, @dt);
SELECT 'Second', DATETRUNC(second, @dt);
SELECT 'Millisecond', DATETRUNC(millisecond, @dt);
SELECT 'Microsecond', DATETRUNC(microsecond, @dt);Output:
---- --------------------------------------
Year 2025-01-01 00:00:00.0000000
------- --------------------------------------
Quarter 2025-07-01 00:00:00.0000000
----- --------------------------------------
Month 2025-08-01 00:00:00.0000000
---- --------------------------------------
Week 2025-08-24 00:00:00.0000000
-------- --------------------------------------
Iso_week 2025-08-18 00:00:00.0000000
--------- --------------------------------------
DayOfYear 2025-08-24 00:00:00.0000000
--- --------------------------------------
Day 2025-08-24 00:00:00.0000000
---- --------------------------------------
Hour 2025-08-24 13:00:00.0000000
------ --------------------------------------
Minute 2025-08-24 13:47:00.0000000
------ --------------------------------------
Second 2025-08-24 13:47:29.0000000
----------- --------------------------------------
Millisecond 2025-08-24 13:47:29.1230000
----------- --------------------------------------
Microsecond 2025-08-24 13:47:29.1234560
How Weeks Behave (and @@DATEFIRST)
Weeks are where things get interesting. The function respects SQL Server’s @@DATEFIRST setting, which defines the first day of the week (1 = Monday, 7 = Sunday, etc.). The above example used my default @@DATEFIRST setting value, which was 7.
We can check our @@DATEFIRST value with a simple SELECT statement:
SELECT @@DATEFIRST;Output:
7
Let’s see what happens when we change our @@DATEFIRST value. First, we’ll explicitly set it to 7:
-- Assume @@DATEFIRST is 7 (Sunday)
SET DATEFIRST 7;
SELECT DATETRUNC(week, '2025-08-24 13:47:29.123') AS WeekStartSunday;Result:
WeekStartSunday
--------------------------------------
2025-08-24 00:00:00.0000000
Since August 24, 2025, is a Sunday, and Sunday is considered the first day of the week, truncation returns that same date at midnight.
Now, let’s change the @@DATEFIRST setting:
-- Switch to Monday as first day of the week
SET DATEFIRST 1;
SELECT DATETRUNC(week, '2025-08-24 13:47:29.123') AS WeekStartMonday;Result:
WeekStartMonday
--------------------------------------
2025-08-18 00:00:00.0000000
With Monday as the first day of the week, the timestamp is snapped back to the Monday of that week instead.
This can cause very different results depending on your environment, so always be clear about what @@DATEFIRST is set to if you’re using DATETRUNC() with weeks.
Impact of Data Type Precision
One thing to keep in mind: DATETRUNC() doesn’t control precision – the data type does. The function always returns the same type as the input, so whatever granularity that type supports is what you’ll see in the result.
For example:
DECLARE @dt DATETIME = '2025-08-24 13:47:29.997';
DECLARE @dt2 DATETIME2(7) = '2025-08-24 13:47:29.9971234';
DECLARE @dt3 SMALLDATETIME = '2025-08-24 13:47:29';
SELECT
DATETRUNC(SECOND, @dt) AS DT_Second,
DATETRUNC(SECOND, @dt2) AS DT2_Second,
DATETRUNC(SECOND, @dt3) AS DT3_Second;Result:
DT_Second DT2_Second DT3_Second
----------------------- -------------------------------------- -------------------
2025-08-24 13:47:29.000 2025-08-24 13:47:29.0000000 2025-08-24 13:47:00
Here’s why:
datetime: Precision is ~3 milliseconds, stored to milliseconds only.datetime2(7): Supports up to 100ns precision, so the display goes to 7 fractional digits.smalldatetime: Only accurate to the minute, so seconds are discarded entirely.
So DATETRUNC() just zeroes out everything below the requested unit. How many digits you see (or even whether seconds are stored at all) depends on the column’s data type, not the truncation function.
Doing it Without DATETRUNC()
Before DATETRUNC() came along, truncating a datetime to the start of a month might involve something like:
SELECT DATEADD(day, 1, EOMONTH(@Date, -1));Not too bad, but certainly more complex than doing it with DATETRUNC(). DATETRUNC() not only makes your code more readable, but also less error-prone. It’s easier to understand at a glance, which is especially helpful when weeks and @@DATEFIRST are involved.