If you’re getting SQL Server error msg 9837 that tells you “The date value is less than the minimum date value allowed for the data type…”, it sounds like you’re using the DATETRUNC() function with the week date part on a date that would cause the result to backtrack to a date that’s earlier than the date type supports.

This is a rare error that only occurs when using the week date part with the DATETRUNC() function on a very small number of early dates.

One way to deal with this error would be to use iso_week instead of week, if that’s suitable for your particular situation.

Example of Error

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

DECLARE @date date= '0001-01-06';
SELECT DATETRUNC(week, @date);

Result:

Msg 9837, Level 16, State 3, Server 3d470416ebec, Line 2
An invalid date value was encountered: The date value is less than the minimum date value allowed for the data type.

Here, the date is 0001-01-06, which is a Saturday, and @@DATEFIRST on my system is set at 7, which is Sunday. Therefore, to truncate to the first day of the week, the DATETRUNC() function would need to go back to the previous Sunday, which is earlier than 0001-01-01, which is the earliest date supported by the date data type.

The result is error 9837.

Solution

As mentioned, one way to address this issue is to use iso_week instead of week for the date part. Using iso_week won’t result in an error, due to the the fact that all date types in SQL Server use Monday for their minimum dates (and the first day of the week in the ISO8601 calendar system is Monday).

Example:

DECLARE @date date= '0001-01-06';
SELECT DATETRUNC(iso_week, @date);

Result:

0001-01-01

Here, the date was truncated to the first day of the week, based on the ISO8601 calendar system. In this case that day is Monday, January 1, 0001 (which is one day later than the day the previous example tried to go back to, and is a date that’s supported by the date data type).