In SQL Server, we can use the DATETRUNC() function to truncate a date/time value to a specified precision.

For example, we could use it to truncate a date value like 2024-10-25 to 2024-01-01, or a time value like 10:35:12 to 10:00:00.

The DATETRUNC() function was introduced in SQL Server 2022 (16.x).

Syntax

The syntax goes like this:

DATETRUNC ( datepart, date )

Where:

• datepart is the precision for which to truncate the input value (for example month, hour, etc). See below for a full list of acceptable values for this argument.
• date can be any expression, column, or user-defined variable that can resolve to any valid date or time type (smalldatetime, datetime, date, time, datetime2, or datetimeoffset). It’s also possible to provide a string literal, as long as it can resolve to a datetime2(7) type.

The field argument can be any of the following:

datepart	Abbreviations	More Info
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y	Works the same as day
day	dd, d	Works the same as dayofyear
week	wk, ww	Truncates to the first day of the week (as defined by the @@DATEFIRST setting. For U.S. English, @@DATEFIRST defaults to 7 (Sunday).
iso_week	isowk, isoww	Truncates to the first day of an ISO Week. In the ISO8601 calendar system, this is Monday.
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs

Basic Example

Here’s an example to demonstrate.

SELECT DATETRUNC(hour, '2024-10-25 10:20:15.1234567');

Result:

2024-10-25 10:00:00.0000000

Here, the time portion of the date has been truncated from 10:20:15.1234567 to 10:00:00.0000000. That’s because I used hour for the first argument.

Here’s the same example, but this time I use hh instead of hour for the first argument:

SELECT DATETRUNC(hh, '2024-10-25 10:20:15.1234567');

Result:

2024-10-25 10:00:00.0000000

Same result, because we can use hour or hh interchangeably (as indicated in the above table).

Explicit Date Conversion

Here’s the same example, but this time the date argument is explicitly cast as a DATETIME2(7) type:

SELECT DATETRUNC(
  hh, 
  CAST('2024-10-25 10:20:15.1234567' AS DATETIME2(7))
  );

Result:

2024-10-25 10:00:00.0000000

Same result again.

Various datepart Arguments

Here it is with different values for the first argument:

DECLARE @date datetime2 = '2024-10-25 10:20:15.1234567';
SELECT 'Year' AS "DatePart", DATETRUNC(year, @date) AS "Truncated Date"
UNION ALL
SELECT 'Quarter', DATETRUNC(quarter, @date)
UNION ALL
SELECT 'Month', DATETRUNC(month, @date)
UNION ALL
SELECT 'Week', DATETRUNC(week, @date)
UNION ALL
SELECT 'Iso_week', DATETRUNC(iso_week, @date)
UNION ALL
SELECT 'DayOfYear', DATETRUNC(dayofyear, @date)
UNION ALL
SELECT 'Day', DATETRUNC(day, @date)
UNION ALL
SELECT 'Hour', DATETRUNC(hour, @date)
UNION ALL
SELECT 'Minute', DATETRUNC(minute, @date)
UNION ALL
SELECT 'Second', DATETRUNC(second, @date)
UNION ALL
SELECT 'Millisecond', DATETRUNC(millisecond, @date)
UNION ALL
SELECT 'Microsecond', DATETRUNC(microsecond, @date);

Result:

DatePart    Truncated Date                        
----------- --------------------------------------
Year                   2024-01-01 00:00:00.0000000
Quarter                2024-10-01 00:00:00.0000000
Month                  2024-10-01 00:00:00.0000000
Week                   2024-10-20 00:00:00.0000000
Iso_week               2024-10-21 00:00:00.0000000
DayOfYear              2024-10-25 00:00:00.0000000
Day                    2024-10-25 00:00:00.0000000
Hour                   2024-10-25 10:00:00.0000000
Minute                 2024-10-25 10:20:00.0000000
Second                 2024-10-25 10:20:15.0000000
Millisecond            2024-10-25 10:20:15.1230000
Microsecond            2024-10-25 10:20:15.1234560

This resulted in different precisions, depending on the value of the datepart argument.

Unsupported datepart Arguments

Some of the datepart arguments supported in SQL Server aren’t supported by the DATETRUNC() function. For example, we can’t use weekday, tzoffset, or nanosecond with the DATETRUNC() function.

Here’s what happens if we try to use one of those datepart arguments:

DECLARE @date datetime2(7) = '2024-10-25 10:20:15.1234567';
SELECT DATETRUNC(weekday, @date) AS "Truncated Date"

Result:

Msg 9810, Level 16, State 11, Server 7639357e2b73, Line 17
The datepart weekday is not supported by date function datetrunc for data type datetime2.

Time Values

We can use DATETRUNC() with time values:

DECLARE @time time = '10:20:15.1234567';
SELECT DATETRUNC(second, @time) AS "Truncated Time"

Result:

10:20:15.0000000

However, when we use time values, we must use an appropriate precision (datepart argument).

Here’s what happens when we use an inappropriate datepart argument:

DECLARE @time time = '10:20:15.1234567';
SELECT DATETRUNC(month, @time) AS "Truncated Time"

Result:

Msg 9810, Level 16, State 10, Server 9782563d6d46, Line 17
The datepart month is not supported by date function datetrunc for data type time.

When the date Argument has a Lower Precision than the datepart Argument

We can’t specify a precision that’s higher than the date we’re operating on. If we do this, we’ll get an error.

Example:

DECLARE @date date = '2024-10-25';
SELECT DATETRUNC(minute, @date);

Result:

Msg 9810, Level 16, State 10, Server 3066fe13dcb5, Line 17
The datepart minute is not supported by date function datetrunc for data type date.