SQL Server issue with ISDATE('3/31/019')-CodePudding

I found the following issue when I try to valid the following date

ISDATE('3/31/019') this return 1, but is not a valid date, when you try to convert(date, '3/31/019') you will get an error

I expect ISDATE('3/31/019') return distinct 1

CodePudding user response：

The documentation states (my added emphasis):

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

So if you run:

SELECT convert(datetime, '3/31/019');

The result with session setting SET DATEFMT mdy succeedes with value '2019-03-31 00:00:00.000'.

Instead of ISDATE, use TRY_CONVERT or TRY_CAST to determine if the expression can be converted to a date. The result will be NULL if not convertable.

CodePudding user response：

According to ISDATE documentation

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

While you are trying to convert to date format.

The below query runs without issue SELECT CONVERT(datetime, '3/31/019')