January 18, 2016 at 11:50 am
Hi,
I am facing an issue as described below.
select * from dbo.xyz
where a in ('a string of multiple values')
and <some condition>
and cast(a as datetime)=cast(b as datetime)
a is varchar(50), and allowed nulls
b is varchar(50), and allowed nulls
I am getting the following error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Now, however, if I a run with the characters in the IN clause using individual select statements I don't get an error.
Regards,
KJ
January 18, 2016 at 11:57 am
Dreamsz (1/18/2016)
Hi,I am facing an issue as described below.
select * from dbo.xyz
where a in ('a string of multiple values')
and <some condition>
and cast(a as datetime)=cast(b as datetime)
a is varchar(50), and allowed nulls
b is varchar(50), and allowed nulls
I am getting the following error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Now, however, if I a run with the characters in the IN clause using individual select statements I don't get an error.
Regards,
KJ
I can't see a question here.
What happens if you run:
select cast(a as datetime)
and
select cast(b as datetime)
separately?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2016 at 12:01 pm
The error itself is pretty self-explanatory, so I won't explain it further. Assuming your columns are meant to store dates and nothing else, what you should do is isolate the values that are not dates and fix them, either by NULLing them out or replacing with an appropriate value. Once that's done, change the data type of the columns to an appropriate date data type. That'll prevent the invalid dates from getting into the table in the first place.
The ISDATE function can help you identify the rows with non-dates. It's covered at https://msdn.microsoft.com/en-us/library/ms187347.aspx. I wouldn't use it in a production query, though, because it will have to run on every row in the table before doing the comparison.
January 18, 2016 at 12:05 pm
By changing the condition from IN (some values) to = (one value), you caused SQL Server to choose a different execution plan.
By using a different execution plan, SQL Server attempts the conversion to datetime for different rows.
If some of those rows contain data that cannot be converted to datetime, then you can get this error.
Today you get the error with IN and do not get the error with =. Tomorrow it may be the other way around.
If a column in a table has data that may or may not represent a datetime value, that is usually a sign of bad database design. You should store datetime values in a column for datetime values only, with data type datetime.
Now while this explains what happens, it does not help you. Perhaps you can explain what you are trying to achieve?
January 18, 2016 at 1:31 pm
Fyi...we've used isdate to eliminate the non-date stuff, but the problem still persists. Kindly, let me know which piece of code is causing the issues, because if I run it using explicit select statements individually with the same query nothing changed, and I get the results, but if I use IN ('string1', 'string2', 'string3'), and it throws an error.
Regards,
KJ
January 18, 2016 at 1:35 pm
ffarouqi (1/18/2016)
Fyi...we've used isdate to eliminate the non-date stuff, but the problem still persists. Kindly, let me know which piece of code is causing the issues, because if I run it using explicit select statements individually with the same query nothing changed, and I get the results, but if I use IN ('string1', 'string2', 'string3'), and it throws an error.Regards,
KJ
Asking us to identify a 'piece of code' without showing us the code you are referring to requires an oracle with a crystal ball. Please provide your SQL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2016 at 1:47 pm
Lolz...that is already there at the top of this post. Yes, the id is different, as I was using my friend's account to post it, but now I am using my account to look at it to understand if someone has answered it. I hope this answers your question, please help if you have any idea on why it is throwing an error?
Regards,
KJ
January 18, 2016 at 1:51 pm
ffarouqi (1/18/2016)
Lolz...that is already there at the top of this post. Yes, the id is different, as I was using my friend's account to post it, but now I am using my account to look at it to understand if someone has answered it. I hope this answers your question, please help if you have any idea on why it is throwing an error?Regards,
KJ
Hugo provided the answer already, I believe.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2016 at 1:53 pm
ffarouqi (1/18/2016)
Fyi...we've used isdate to eliminate the non-date stuff, but the problem still persists. Kindly, let me know which piece of code is causing the issues, because if I run it using explicit select statements individually with the same query nothing changed, and I get the results, but if I use IN ('string1', 'string2', 'string3'), and it throws an error.Regards,
KJ
First, isdate is not very reliable. It checks whether a value is able to be converted to any of the date/time data types. Which does not mean that it can be converted to the one you are trying to convert to.
Second, depending on how you wrote your query it is still very much possible to get a plan that converts data before testing the isdate condition. SQL Server is a declarative language with very little guarantees on evaluation order. This is why we need to see your code before we can comment. And "WHERE <some condition>" does not qualify as posting your code, because I am pretty sure that if I copy and paste that, I will get a syntax error, not a conversion failed error. (In fact, the actual recommendation is to post CREATE TABLE statements, INSERT statements, *and* your failing statement - a full set of repro code that we can just copy and paste into a query window and get the same error you are seeing).
Third, please reread my previous reply. You are trying to make us patch the result of a decision that might not be correct, instead of asking us to help you reevaluate that decision.
January 18, 2016 at 2:35 pm
Hugo,
I cannot post each, and everything due to certain reasons which I hope you understand. I'll provide you with the select query with the actual values replaced with dummy data. I am not expecting you guys execute the code, and provide me an answer that it is failing. I only want to understand where is it failing logically, and throwing an error.
select * from dbo.xyz
where a in ('v','x', 'y', 'z')
and b is null
and c is null
and isdate(p) = 1
and isdate(q) = 1
and cast(k as datetime) = cast(l as datetime)
-- Please note that the column l, and k contains values such as 8.00 etc instead of actual date/time values also,
so this could be one of the reason why it is throwing an error, but that's what I need to understand is it bad data that is causing conversion error, and how does it logically works in this statement.
k as varchar(50), null
l as varchar(50), null
Regards,
KJ
January 18, 2016 at 2:58 pm
So, to understand, you have a varchar(50) column that contains values such as 8.00, "Bob", 01/01/2016, and so forth.
You want to only get the rows that contain dates contained in this column, correct?
In the above example, the row that contains '01/01/2016' would be returned, and 8.0, Bob would be filtered?
Again, Hugo already explained this.
There is no precedence of the operations in a WHERE clause
where a in ('v','x', 'y', 'z')
and b is null
and c is null
and isdate(p) = 1
and isdate(q) = 1
and cast(k as datetime) = cast(l as datetime)
You are expecting the ISDATE to be executed first. It may or may not get executed.
If this is an EVA design, then you may need to join other tables or include another column(s) that tells you that this may be a date value.
As others have pointed out, this is not a very efficient design.
And, as a test, run this code:
SELECT ISDATE('007/01/2016')
Doesn't look like a valid date, but ISDATE thinks it is.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 18, 2016 at 4:08 pm
ffarouqi (1/18/2016)
Hugo,I cannot post each, and everything due to certain reasons which I hope you understand. I'll provide you with the select query with the actual values replaced with dummy data. I am not expecting you guys execute the code, and provide me an answer that it is failing. I only want to understand where is it failing logically, and throwing an error.
select * from dbo.xyz
where a in ('v','x', 'y', 'z')
and b is null
and c is null
and isdate(p) = 1
and isdate(q) = 1
and cast(k as datetime) = cast(l as datetime)
-- Please note that the column l, and k contains values such as 8.00 etc instead of actual date/time values also,
so this could be one of the reason why it is throwing an error, but that's what I need to understand is it bad data that is causing conversion error, and how does it logically works in this statement.
k as varchar(50), null
l as varchar(50), null
Regards,
KJ
In the code you posted, you test isdate on columns p and q, and then try to convert columns k and l to datetime. If that is a good representation of your actual code, then the explanation of your problem is simple: you have rows where p and q pass the isdate test, but k and/or l do not.
If you messed up when anonymizing your code and you do actually convert the same columns you test, then the explanation is as I have given in my previous answers. Either there are values that are accepted by isdate but still fail conversion, or SQL Server has chosen an execution plan that converts the data type in all rows before applying the filter.
The last problem is somewhat easy to work around. Just embed the conversion in a CASE expression, which is one of the few constructions in T-SQL that do actually guarantee order of execution (with a few exceptions that do not apply here). But then you can still face the shortcomings of ISDATE. On SQL Server 2012 or before, the only way to get around that is to use complicated LIKE patterns to verify that the data is exactly in the format it should be in. Not pretty at all.
If you are on SQL Server 2014 or better, then use TRY_CAST or TRY_CONVERT instead, as a far easier solution.
All that being said, I still maintain that you are probably working with badly designed tables. But I already offered help with that twice and you turned it down both times, so you apparently rather keep combatting the effects instead of fixing the root cause of your problems - which is of course your good right.
You write that you only want to understand where it is failinig and are not looking for a fix. I think I explained the reason for the error three times now, and others have weighed in as well, so I hope you are happy with the help you received.
January 19, 2016 at 2:48 pm
Check and fix incorrect data, isdate function can help you.
Change data type to datetime if you can... Avoid cast() in where clause: It is a performance killers!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply