sqldatetime overflow exception c#.NET

Question

string con = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

        SqlConnection cn = new SqlConnection(con);
        string insert_jobseeker = "INSERT INTO JobSeeker_Registration(Password,HintQuestion,Answer,Date)"
      + " values (@Password,@HintQuestion,@Answer,@Date)";

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = insert_jobseeker;

 cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 50));
            cmd.Parameters["@Password"].Value = txtPassword.Text;
            cmd.Parameters.Add(new SqlParameter("@HintQuestion", SqlDbType.VarChar, 50));
            cmd.Parameters["@HintQuestion"].Value = ddlQuestion.Text;
            cmd.Parameters.Add(new SqlParameter("@Answer", SqlDbType.VarChar, 50));
            cmd.Parameters["@Answer"].Value = txtAnswer.Text;

            **cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
            cmd.Parameters["@Date"].Value = System.DateTime.Now**

I got error that

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

What's the solution for this ?

Beebe

very strange, System.DateTime.Now must be in that range, are you sure you are not using somewhere DateTime.Min/Max? – Plagiarism

do u have default value set for your date in your Database?? – Grenville

What is your system locale? Can you check your regional DateTime settings? It's possible that your date is not being interpreted correctly; at face value, the code looks okay. One way to check if this is the case is to try: cmd.Parameters["@Date"].Value = System.DateTime.Now.ToString("yyyyMMdd HH:mm:ss") - i.e. pass an invariant DateTime format. Once you've identified this as an issue, there are several things you can do... – Byplay

Answer 1

Try changing the Type of @Date on the SQL Server side to DATETIME2(7)

Then in your code use this line instead:

cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime2)); 

Your code looks okay as shown but possibly something is going on with the conversion due to a localization issue or something wrong with your Region/Time settings so see if this works.

Subservience

Answer 2

If you are working with SQL Server 2008 and above, you can do this:

Step 1: Change your @Date datatype from DATETIME to DATETIME2(7)

Step 2: In your codebehind, use this:

SqlDbType.DateTime2

Grenville

Answer 3

"Date" is a keyword, do not use it as a column name. If you have to, enclose it in [] in your insert statement: [Date] But it would be better to change it to something else, for example "RegistrationDate".

Geilich