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 ?

very strange, `System.DateTime.Now` must be in that range, are you sure you are not using somewhere `DateTime.Min/Max`? — sll, Mar 22 '13 at 08:56
do u have default value set for your date in your Database?? — Praveen Nambiar, Mar 22 '13 at 08:56
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... — dash, Mar 22 '13 at 09:09

score 2 · Answer 1 · answered Mar 22 '13 at 09:11

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.

score 0 · Answer 2 · answered Mar 22 '13 at 09:02

0

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

answered Mar 22 '13 at 09:02

Praveen Nambiar

4,852
1
22
31

score 0 · Answer 3 · answered Mar 22 '13 at 09:12

0

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

answered Mar 22 '13 at 09:12

svenv

313
2
9

sqldatetime overflow exception c#.NET

3 Answers3

Linked