If you’re getting SQL Server error msg 9810 that tells you that the datepart “is not supported by date function datetrunc for data type“, it’s probably because you’re using an invalid datepart argument when using the DATETRUNC() function.

For example, this error can occur when using a time date part on a date value (i.e. one that doesn’t have a time component). Conversely, it can also occur when using a date date part on a time value (i.e. one that doesn’t have a date component).

This error can also occur when using an unsupported date part, regardless of the date/time value. Unsupported date parts include weekday, tzoffset, and nanosecond.

To fix this issue, be sure to use a valid date part for the date/time value when using the DATETRUNC() function.

Example of Error

Here’s an example of code that produces the error:

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.

I got the error because I tried to use the minute date part with a date value. The date data type doesn’t support time components like minute, hour, seconds, etc. Therefore the DATETRUNC() function can’t truncate the value to that precision, and so the error is returned.

We’ll get the same error if we try to use a date date part against a time value:

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

Result:

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

We’ll also get the error if we use an unsupported date part, such as weekday, tzoffset, or nanosecond:

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

Result:

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

In this case I supplied the weekday date part, but that date part isn’t allowed.

Solution

Regardless of the exact reason for the error, the solution is the same: provide a date part that is supported for the given data type.

For example, we could resolve the first example above like this:

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

Result:

2024-10-01

This assumes that we want to truncate it to the month.

And we could resolve the second example like this:

DECLARE @time time = '10:20:15.1234567';
SELECT DATETRUNC(minute, @time);

Result:

10:20:00.0000000

That assumes we want to truncate to the minute.

As for the third example:

DECLARE @date datetime2(7) = '2024-10-25 10:20:15.1234567';
SELECT DATETRUNC(iso_week, @date);

Result:

2024-10-21 00:00:00.0000000

In this case I truncated it to the ISO week.

String Literals

If we use a string literal for the date, we might find that we don’t get the error, due to the fact that SQL Server implicitly converts the string literal to an appropriate data type.

Here’s what happens when we use a string literal for the first example:

SELECT DATETRUNC(minute, '2024-10-25');

Result:

2024-10-25 00:00:00.0000000

And the second example:

SELECT DATETRUNC(month, '10:20:15.1234567');

Result:

1900-01-01 00:00:00.0000000

It’s the same with date and time literals:

SELECT 
  DATETRUNC(minute, {d '2024-10-25' }) AS "Date",
  DATETRUNC(minute, {t '10:20:15.123' }) AS "Time";

Result:

Date                    Time                   
----------------------- -----------------------
2024-10-25 00:00:00.000 2023-12-03 10:20:00.000

In this case, the time value was converted using the current date.