I have a view where I have a column having date in varchar formet and in
Msg 241, Level 16, State 1, Line 2 Conversion failed when converting
date and/or time from character string.
select * from test1.dbo.abcd order by CONVERT(date,ddate,103)
Since this works fine for me:
ORDER BY CONVERT(DATE, LEFT(ddate, 10), 103);
What I suspect has happened is that the data pushed into your view has trailing characters that you can't see (e.g. carriage returns) or non-printing characters. You can check for values that exceed 10 characters by:
SELECT ... WHERE LEN(ddate) > 10
If that still doesn't work, then perhaps the data really is bad, e.g. maybe it is:
In which case the only thing you can really do is push back on the owners of the table/view to get correct data in the first place, or try to filter out using:
SET DATEFORMAT DMY; ... WHERE ISDATE(ddate) = 1
As for the title in your question,
ISDATE returns 0 in that case because by default that's not a date - in most cases SQL Server is set up using US English and a date format of MDY, therefore it expects date strings in the format
13/01/2010 is the first day of the 13th month in 2010. My memory is sketchy but I am certain there wasn't a 13th month that year.
As others have suggested, the real fix for this is to store your data as date/time, not strings, and to pass in string literals in an unambiguous format, e.g.