July 16, 2010 at 11:22 am
I have a strange issue where I have a field I am bringing in then checking if it is a date using IsDate and then casting the values as Date if it passess the IsDate test. My problem is the field passes the IsDate function logic but when cast as a date (I also used convert still didn't work) it gives me the error:
Conversion failed when converting date and/or time from character string.
Here is the logic I am using:
Select A_bunch_of_Fields,
CASE ISDate(LEFT(MyField,10))
WHEN 0 Then '1900-01-01'
ELSE CAST(LEFT(MyField,10) AS DATE)
END
AS MyField,
A_bunch_of_other_fields
From ...
Please help this one really has me stumped -- this code was working seemlessly for months so I know there is some new data causing this issue but I need to find a way to identify it.
July 16, 2010 at 1:40 pm
Hi Joe this wasn't quite the answer but your response was the catalyst to get me thinking in a different way which eventually got me the solution. It turned out to be dirty daty i.e. one date record was represented missing a digit off the year i.e. '010-07-12' if you try to cast this you get the conversion failure but if you put it against isDate it returns it as a valid date.
I found the dirty data by doing a negative logic select asking for anything which didn't look like a proper date but passed the isdate function as a proper date my code looked like this.
select MyDateStringField
from [MyDB].[dbo].MyTable
where ISDATE(MyDateStringField) = 1
and MyDateStringField not like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
Thanks very much for your response.
Cheers,
March 14, 2011 at 9:57 am
I am having a similar problem. FYI: ItemValue =nvarchar(4000) and the data is either a valid date or NULL
Here is the issue. I get the following error...
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
..when I run the code below
select cast(ItemValue as date)
from tasks t
join ItemData i on t.TaskId = i.TaskId and t.GenId = i.TaskGenId
join Control c on i.CGenId = c.CGenId
where t.FormId = 1 and c.TypeId = 1
If I add the next line, I get dates... but have also gotten the same error (don't know why)
and ISDATE(i.ItemValue)= 1
If I change the last line to ISDATE(i.ItemValue)= 0 it returns all NULLS (as expected)
However, THIS IS THE ODD PART, if I change the last line to the one below, I don't get the error and it returns NULLS and dates
and (ISDATE(i.ItemValue)= 0 or ISDATE(i.ItemValue)= 1)
THIS IS BOOLEAN CORRECT? BY ADDING THIS LINE, I MEET BOTH CONDITIONS. WHY IS THIS DIFFERENT THAN THE EXAMPLE THAT GIVES ME THE ERROR?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply