LINQ converting date, need to handle bad date

I'm using LINQ-to-SQL and need to order by a date field. The date field is st开发者_StackOverflow中文版ored as text and could have anything in it since it is user entered data. I need to handle cases where an invalid date was placed in there.

For example, a date of "02/23/0000" returns:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I need to avoid errors and I don't care where an invalid date like this gets sorted to. The parameters of the project mean I can't modify my source data, only read from it.

Here is an example LINQ statement.

from x in dbo_myTable
orderby Convert.ToDateTime(x.MyDateField)
select x

Do you definitely need to perform the ordering in the database? In this situation I would strongly consider pulling all of the data, then performing some conversion in .NET code where you have a great deal more control over what's going on, and then order it still in .NET code.

Ultimately, dealing with screwed up data in this sort of situation is tricky - the more control you have, the better.

You can do something like this (Based on your comment that they are all 10 characters)

from x in dbo_myTable
orderby x.MyDateField.Substring(6, 4), x.MyDateField.Substring(0, 2), x.MyDateField.Substring(3, 2)
select x

First is first; why cant you change the database to fix you obviously brokes table structure? Why cant you sanitize input when its going into the database? Why are you not validating user input?

Now for the answer; your only option is attempt to parse the date (with DateTime.TryParse or DateTime.TryParseExact) and set a default value of your choosing if it fails to parse:

from x in dbo_myTable
orderby TryConvertToDateTime(x.MyDateField)
select x

private DateTime TryConvertToDateTime(string dt)
{
   DateTime rtn = DateTime.MinValue; // or whatever you want your default to be
   DateTime.TryParse(dt,out rtn);
   return rtn;
}