Date to SQL in LINQ to Entities

Using LINQ to Entities. This code in an anonymous type

MessageDate = DateTime.Parse(email.MessageDate),

Is throwing this error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

MessageDate is a SQL DateTime and email.MessageDate is a string开发者_运维技巧.

I tried this but it didn't help:

MessageDate = DateTime.Parse(string.Format("{0:yyyy-MM-dd HH:mm:ss.fff}", DateTime.Parse(email.MessageDate)))

Any assistance is appreciated.

More info:

The string value of email.MessageDate is "Wed, 15 Jun 2011 13:29:00 -0400"

Also, if I just do

MesssageDate = Date.Now

I get the same error.

Also, it's SQL Server 2005

More testing: I set a breakpoint to see what resolved to true

var x = 1;
if (DateTime.Now > System.Data.SqlTypes.SqlDateTime.MinValue.Value)
    x = 1; // True
if (DateTime.Now < System.Data.SqlTypes.SqlDateTime.MinValue.Value)
    x = 1;
if (DateTime.Now > System.Data.SqlTypes.SqlDateTime.MaxValue.Value)
    x = 1;
if (DateTime.Now < System.Data.SqlTypes.SqlDateTime.MaxValue.Value)
    x = 1; // True
if (DateTime.Now < DateTime.MinValue)
    x = 1;
if (DateTime.Now > DateTime.MinValue)
    x = 1; // True
if (DateTime.Now < DateTime.MaxValue)
    x = 1; // True
if (DateTime.Now > DateTime.MaxValue)
    x = 1;

So it looks to me that DateTime.Now is within all the valid ranges.

Thank you, Mark

It is parsing the string fine but unfortunately the valid .net date ranges are not the same as the valid SQL date ranges. email.MessageDate is probably set to DateTime.MinValue which is earlier than the earliest valid SQL date.

If you are using SQL 2008 you can use datetime2 instead of datetime type. If you can't change your SQL datatype then I recommend rolling your own "smart" DateTime parser.

This was ridiculously hard to find. Here is the answer:

LINQ to Entities has some problem with not providing the Date information to a table that has DateTime fields. So even if you don't need to put data in a DateTime field the Submit won't work unless you do. So all I did, on the fields where I did not have data to provide, was, for example, this...

MessageDeliveryTime = null,

Now, the other thing that got me was this. I have a column in the database name Created that auto-populates with getdate(). LINQ to Entities doesn't care about your little default values. You must pass in something; so just do this:

Created = DateTime.Now

I hope that the hours of my life that are gone don't go in vain and this will help someone out.

Mark