Convert DateTime to DateTime that works with SQL Server?

Question

I am trying to add some dates to some asp.net membership fields (LastLoginDate, LastPasswordChangedDate, etc)

so what I did was

DateTime sendDate = new DateTime(1754, 1, 1, 12, 0, 0);

then I tired to use linq to sql and add them.

It comes back with an exception

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

I am not sure how to convert it into something that will be let in.

DateTime dateFields = new DateTime(1800, 1, 1, 12, 0, 0);
aspnet_Membership membership = new aspnet_Membership();
                membership.ApplicationId = applicationId;
                membership.UserId = userId;
                membership.Password = password;
                membership.PasswordFormat = passwordFormat;
                membership.PasswordSalt = base64Salt;
                membership.MobilePIN = null;
                membership.Email = email;
                membership.LoweredEmail = email.ToLower();
                membership.PasswordQuestion = null;
                membership.PasswordAnswer = null;
                membership.IsApproved = isApproved;
                membership.IsLockedOut = false;
                membership.CreateDate = dateCreated;
                membership.LastLoginDate = dateFields;
                membership.LastPasswordChangedDate = dateFields;
                membership.LastLoginDate = dateFields;
                membership.FailedPasswordAttemptWindowStart = dateFields;
                membership.FailedPasswordAnswerAttemptCount = 0;
                membership.FailedPasswordAnswerAttemptWindowStart = dateFields;
                membership.FailedPasswordAnswerAttemptCount = 0;
                membership.Comment = null;


                // from asp.net mvc unleashed book.
                GenericRepository.Create<aspnet_Membership>(membership);

Answer 1

Possibly you're sending it in the wrong order.

What does your SQL clause look like? If you're just setting a SqlParameter (as you should be) life should be quite okay, assuming the parameters of the DateTime are set correctly.

Answer 2

Maybe there's just one more DateTime field that you're not yet filling?? In that case, it would default to 01/01/0001 and that's outside the DATETIME range for SQL Server.

If you're on SQL Server 2008, you could avoid this be using the DATETIME2 type - this has a valid date range of 01/01/0001 through 12/31/9999

Marc

Answer 3

Where do you get "dateCreated" from in the c#? What value is it?

And you are not setting "LastLockoutDate"... you actually have "LastLoginDate" twice. What values are being set for LastLockoutDate?

It looks like "dateFields" is correct for datetime in SQL Server terms (which is used by aspnet_Membership), perhaps the error comes from elsewhere...

Answer 4

1. Check that you allow NULL for the DateTime columns.
2. Also check the locales/date formats on the SQL server. It may just expect day, month or year in a different order (e.g. m/d/y vs dd/mm/yyy)

Answer 5

Try using this function to convert with.

public static string GetDateTimeSQLString(DateTime SomeTime)
{
    //yyyy-MM-dd HH:mm:ss
    string SqlTime = SomeTime.Year.ToString() + "-" + SomeTime.Month.ToString() + "-" + SomeTime.Day.ToString() + " " + SomeTime.Hour.ToString() + ":" + SomeTime.Minute + ":" + SomeTime.Second.ToString();
    return SqlTime;
}