CODEWITHSUNDEEP
asp.netsql-server
chobo2
chobo2

Reputation: 85715

Convert DateTime to DateTime that works with SQL Server?

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);

Upvotes: 0

Views: 940

Answers (5)

EKS
EKS

Reputation: 5623

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;
}

Upvotes: 0

DmitryK
DmitryK

Reputation: 5582

  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)

Upvotes: 0

gbn
gbn

Reputation: 432180

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...

Upvotes: 1

marc_s
marc_s

Reputation: 754230

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

Upvotes: 2

Noon Silk
Noon Silk

Reputation: 55062

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.

Upvotes: 0

Related Questions