Question Regarding ISDATE Function and DATENAME Functions

July 1, 2021 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

We have had an issue this morning with a report failing due to the following error:

"Conversion failed when converting date and/or time from character string"

The issue is that in the database we had a value (in a VARCHAR field) of: ‘31.07/2021’

The report uses an ISDATE check to verify that the value is actually a date, and if so then pulls it apart using DATENAME functions (to get something thta ends up in a format similar to "DD-MMM-YYYY".

The issue is that even though ISDATE returns 1 for this value (indicating that it is interpreted by SQL Server as a date) the subsequent DATENAME functions throw the conversion error.

This is on SQL Server 2008 R2, but also tested on SQL 2012 & SQL 2016.

Repro code:

set dateformat dmy;

-- Date value from the database - note the mixture of '.' and '/' as separators
declare @the_date varchar(20) = '31.07/2021';

-- should return 1 - SQL interprets the above as a date
select isdate(@the_date);

-- All three of the DATENAME functions fail - why?
select DATENAME(dd,@the_date);

select DATENAME(MONTH,@the_date);

select DATENAME(YYYY,@the_date);

I would have expected that if a value returns 1 from ISDATE then any subsequent date-related functions should interpret the value correctly. Obviously I am missing something. 🙂

If anyone can explain this it would be very helpful.

asked 2021-07-01 by Owen West

Answer

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);

answered 2021-07-02 by Andy Mallon