If you’re getting an error that reads Argument data type datetime2 is invalid for argument 1 of isdate function, it’s because you’re passing a datetime2 value to the ISDATE() function, but this function doesn’t work with datetime2 values.

To fix this issue, either pass a valid date type or use the work around below to provide similar functionality that works with datetime2 values.

The Error

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

DECLARE @thedate datetime2 = '2035-01-30 22:15:45.1234567';
SELECT ISDATE( @thedate );

Result:

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

The error is quite self-explanatory. It tells us that the datetime2 type is invalid for argument 1 of the ISDATE() function. This function only accepts one argument, and so we simply can’t pass a datetime2 value to this function.

Solution 1

One solution is to pass a date type that the ISDATE() function accepts as a date. The Microsoft documentation states that the ISDATE() function:

Returns 1 if the expression is a valid datetime value; otherwise, 0.

Therefore, we could modify our example to the following:

DECLARE @thedate datetime = '2035-01-30 22:15:45.123';
SELECT ISDATE( @thedate );

Result:

1

Mind you, if we can manipulate the date that easily it kind of defeats the purpose of checking whether it’s a date, as we probably already know the answer. That said, there could be cases where we’re able to ensure a datetime value is passed instead of its datetime2 equivalent.

Solution 2

If we must use a datetime2 expression, then we can use code like the following:

DECLARE @thedate datetime2 = '2035-01-30 22:15:45.1234567';
SELECT IIF( TRY_CAST( @thedate AS DATETIME2 ) IS NULL, 0, 1 );

Result:

1

The TRY_CAST() function returns the converted value if it can be converted, otherwise it returns NULL. The IIF() function is a shorthand way for writing a CASE expression, and we can use it to output 1 when the value is returned (i.e. a date) and 0 when NULL is returned (i.e. not a date).

We can also replace TRY_CAST() with TRY_CONVERT() or even TRY_PARSE(). Bear in mind that TRY_PARSE() can understand a wider range of values as dates. For example, it can parse Thursday 19 Jan 2023 as a datetime2 whereas TRY_CAST() and TRY_CONVERT() can’t.