If I run a the following statement I get the message 241 from the date at '2011/31/01 00:00:00' (the second order attribute). The other dates (same format) are converted correct, even if I replace the "wrong" date with one of the others.
If I run the same statement at SQL 2005 or set the compatibility of my database to SQL 2005 the statement works fine. Also it works if I add a CAST() or change the date format to 'yyyymmdd'.
I agree, the statement looks wired, but I have to use a similar (a little more complex) to get the required result.
SELECT Id, FromDate, ToDate
WHERE ISNULL(ToDate, '2999/31/12 23:59:59') >= '2010/01/06 00:00:00'
ORDER BY ABS(DATEDIFF(day, '2005/01/02 00:00:00', ISNULL(FromDate, '1899/31/12 00:00:00'))),
ABS(DATEDIFF(day, '2011/31/01 00:00:00', ISNULL(ToDate, '2999/31/12 23:59:59'))),
ISNULL(FromDate, '1899/31/12 00:00:00') DESC