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 date
, time
, or 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 date
value:
DATENAME ( datepart , date )
You have these three queries, where @the_date
is a varchar
value:
SELECT DATENAME(dd,@the_date);
SELECT DATENAME(MONTH,@the_date
SELECT DATENAME(YYYY,@the_date);
But because DATENAME()
requires a date input, it will implicitly do this:
SELECT DATENAME(dd,CONVERT(date,@the_date));
SELECT DATENAME(MONTH,CONVERT(date,@the_date));
SELECT DATENAME(YYYY,CONVERT(date,@the_date));
And that implicit conversion to date
fails with the error you're seeing. This means that the value converts successfully to either a time
or datetime
successfully. And because it converts to one of them successfully, ISDATE()
returns true.
We can use TRY_CONVERT()
instead of ISDATE()
. 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 ISDATE()
.
By doing this, we see that the conversions to date
and time
fail, but the conversion to datetime
succeeds.
SET DATEFORMAT dmy;
DECLARE @the_date varchar(20) = '31.07/2021';
SELECT TRY_CONVERT(date,@the_date),
TRY_CONVERT(time,@the_date),
TRY_CONVERT(datetime,@the_date);
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 date
and 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 date
or datetime
:
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),
TRY_CONVERT(datetime,@the_date_varchar)
);
SELECT @the_date_date
SELECT DATENAME(dd,@the_date_date),
DATENAME(MONTH,@the_date_date),
DATENAME(YYYY,@the_date_date);