Vikky Vikky - 2 months ago 9
SQL Question

Why does SQL Server return 0 for ISDATE('13/01/2010')

I have a view where I have a column having date in varchar formet and in

dd/MM/yyyy

when I tried to use order by it gives error that


Msg 241, Level 16, State 1, Line 2 Conversion failed when converting
date and/or time from character string.


The query is

select * from test1.dbo.abcd order by CONVERT(date,ddate,103)


Some dates on which it return 0 is
13/01/2010


18/12/2009
20/10/2009
21/06/2010
19/10/2009
18/11/2010
29/10/2009
29/10/2009
18/03/2010
15/03/2010
15/03/2010
15/03/2010
15/04/2010
15/04/2010
15/04/2010
15/04/2010
30/03/2010
14/05/2010
14/01/2010
23/10/2009
20/10/2009
27/10/2009
27/10/2009
20/07/2009
20/04/2010
13/08/2010
30/06/2011
23/07/2010
27/08/2010
24/09/2010


Can any one tell me why its returning 0 and how to sort it?

Answer

Since this works fine for me:

SELECT CONVERT(date,'13/01/2010',103);

Try:

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:

13/O1/2010

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 mm/dd/yyyy. 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. YYYYMMDD.

Comments