The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value?

Question

I am trying to insert date in my database table through a textbox. But even if I am converting the string into Datetime I am still getting this error :

"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value".

I have taken datetime datatype in my database. This is my code :

try
{
    SqlCommand cmd = new SqlCommand(@"INSERT INTO tblProject(project_starting_date,project_ending_date)values(@projectstartingdate,@projectendendingdate)", objconn);
    //cmd.Parameters.AddWithValue("@projectstartingdate", DateTime.Parse(txtStartingdate.Text).ToString());
    //cmd.Parameters.AddWithValue("@projectendendingdate", DateTime.Parse(txtProjectendingdate.Text).ToString());
    DateTime stdate;
    if(DateTime.TryParse(txtStartingdate.Text, out stdate))
    {
        //cmd.Parameters.AddWithValue("@projectstartingdate",stdate);
        SqlParameter projstrtdate = new SqlParameter("@projectstartingdate", SqlDbType.DateTime);
        projstrtdate.Value = stdate;
        cmd.Parameters.Add(projstrtdate);
    }
    DateTime enddate;
    if (DateTime.TryParse(txtProjectendingdate.Text, out enddate))
    {
        //cmd.Parameters.AddWithValue("@projectendendingdate", enddate);
        SqlParameter projenddate = new SqlParameter("@projectendendingdate", SqlDbType.DateTime);
        projenddate.Value = enddate;
        cmd.Parameters.Add(projenddate);
    }
    if (objconn.State == ConnectionState.Closed)
    {
        objconn.Open();
    }
    norowaffected = cmd.ExecuteNonQuery();
    objconn.Close();
}
catch (Exception ex)
{
    Response.Write( ex.ToString());
}

Please guide me where I am doing wrong?

Answer 1

I had the same problem before. I fixed it by deleting the corresponding columns in the data base and recreate it with the correct format "so date time for you". problem was fixed. it seems there is still information in the data base telling what format it was before.

Answer 2

Probably your locale configuration is trying to convert the data string in a wrong unexpected format.

Try with the following:

if(DateTime.TryParse(txtStartingdate.Text, out stdate)
{
    SqlParameter projectStartingDateParam = new SqlParameter("@projectstartingdate", SqlDbType.DateTime);
        projectStartingDateParam.Value = stdate;
    cmd.Parameters.Add(projectStartingDateParam);
}

Do the same with "projectendingdate". Create a SqlParameter with SqlDbType equals to SqlDbType.DateTime and add it to the query command (cmd variable).

If this dosen't work, double check your table structure if it's in DateTime format. Do a manually insert directly in database via SQL Server Management Studio.