Hi Experts,

I am getting a error when i am writting this query

Select * From Status_Checklist

Order By Convert(DateTime, Medical_Advisor_Received_Date, 103) DESC


In the database "Medical_Advisor_Received_Date" field is defined as
varchar(10) and many rows are null for this field. I am working on SQL
server 2005.

So if any one havin any idea about this then please me.

Regards
Dinesh

The problem is that you have some strings that are invalid British format
dates. NULLs are not a problem. See this example:

SELECT CONVERT(DATETIME, x, 103) AS mydate
FROM (SELECT '20/01/2008'
UNION ALL
SELECT '22/01/2008'
UNION ALL
SELECT NULL
UNION ALL
SELECT NULL) AS T(x)
ORDER BY mydate

You can use the ISDATE function to check if the string is valid date format
(but your language settings have to match the date format in the strings):

SET LANGUAGE british

SELECT CASE WHEN ISDATE(x) = 1
THEN
CONVERT(DATETIME, x, 103)
END AS mydate
FROM (SELECT '20/01/2008'
UNION ALL
SELECT '22/01/2008'
UNION ALL
SELECT NULL
UNION ALL
SELECT '2a/01/AAAA' -- Invalid date string
UNION ALL
SELECT NULL) AS T(x)
ORDER BY mydate

HTH,

Plamen Ratchev
http://www.SQLStudio.com