Fix Msg 8116 “Argument data type date is invalid for argument 1 of substring function” in SQL Server

If you’re getting SQL Server error Msg 8116 with text that reads Argument data type date is invalid for argument 1 of substring function, it’s because you’re passing the wrong data type to a function – in this case, the SUBSTRING() function.

You could also see the same error (Msg 8116) in many other contexts – it’s not limited to the SUBSTRING() function.

Example of the Error

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

DECLARE @d date = '2020-03-04';
SELECT SUBSTRING(@d, 1, 4);

Result:

Msg 8116, Level 16, State 1, Line 2
Argument data type date is invalid for argument 1 of substring function.

Here, I passed a date type as the first argument to SUBSTRING(), but this is not allowed.

The SUBSTRING() function accepts a character, binary, text, ntext, or image expression as its first argument. If you pass an argument that’s not one of those accepted types, then the above error occurs.

As mentioned, it can happen with other functions too. Either way, it means that you’re passing the wrong data type to the function.

Solution 1

First, if you’re passing a column, check that you’ve got the right column. The solution could be a simple matter of replacing the incorrect column name with the correct column name.

Same if you’re passing a variable – check that it’s the right variable. You might be able to solve this issue by replacing the incorrect variable with the correct one.

Solution 2

If you’re sure that you’ve got the correct column name/variable, one way to fix this error is to convert the argument to the appropriate data type.

For example, we could adjust the above example to the following:

DECLARE @d date = '2020-03-04';
SELECT SUBSTRING(CAST(@d AS varchar(10)), 1, 4);

Result:

Solution 3

But before we go converting anything, sometimes it pays to step back and think if there’s a simpler way to produce the desired outcome.

For example, in the above example, all we’re trying to do is extract the year from the date. In this case, we might be better off doing away with the SUBSTRING() function altogether, in favour of the YEAR() function:

DECLARE @d date = '2020-03-04';
SELECT YEAR(@d);

Result:

Another way to do it would be to use the FORMAT() function:

DECLARE @d date = '2020-03-04';
SELECT FORMAT(@d, 'yyyy');

Result: