SQL Server data types & functions can be tricky.
Let's start with the
ISDATE() function. The docs describe the function fairly concisely:
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
Even though the function is
ISDATE(), it might be better called
ISDATEORTIMEORDATETIME(). I guess the latter name just didn't make it past the Marketing department.
This means that the string you have ('31.07/2021') would translate to either a
datetime value. If it successfully is converted to any of those values, then
ISDATE() will return true.
If we look at the docs for the
DATENAME() function, we see that in the syntax, the second parameter is specifically a
DATENAME ( datepart , date )
You have these three queries, where
@the_date is a
DATENAME() requires a date input, it will implicitly do this:
And that implicit conversion to
date fails with the error you're seeing. This means that the value converts successfully to either a
datetime successfully. And because it converts to one of them successfully,
ISDATE() returns true.
We can use
TRY_CONVERT() instead of
TRY_CONVERT() is more precise because it will only try to convert to the very specific conversion that is specified, rather than the loosey-goosey logic of
By doing this, we see that the conversions to
time fail, but the conversion to
SET DATEFORMAT dmy;
DECLARE @the_date varchar(20) = '31.07/2021';
If you are converting strings to dates, you really should do the conversion using explicit format codes, as the only truly safe implicitly converted format is
YYYYMMDD. All other date formats are subject to variations.
That said, if you are willing to roll the dice on date formats, or are getting varied & unpredictable date formats, you could roll the dice on the implicit conversions, and work around the problem you're seeing.
You can simply take your
varchar date strings, and throw them into a
date value, then pass that
date value to
DATENAME. You can leverage
TRY_CONVERT to try to convert the string to both
datetime and use one if it works. Something like this will work with all sorts of whacky formats, as long as it can be converted to EITHER
SET DATEFORMAT dmy;
DECLARE @the_date_varchar varchar(20) = '31.07/2021';
DECLARE @the_date_date date;
SELECT @the_date_date = COALESCE(TRY_CONVERT(date,@the_date_varchar),