All we need is an easy explanation of the problem, so here it is.
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.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
ISDATE() can be a tricky function, and you probably are better off using the explicit
TRY_CONVERT() function for testing if a value is properly formatted as a date.
My guess is that example value you specified is being treated as a date with time which is why
ISDATE() returns true, similar to the issue in this post.
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
SELECT DATENAME(dd,@the_date); SELECT DATENAME(MONTH,@the_date SELECT DATENAME(YYYY,@the_date);
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
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'; 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
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), 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);
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂