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 ?

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.

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

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