Insert Null value into DateTime column in SQL server from ASPX application


We have following web form asking user to enter name and joining date


In SQL express we are having following columns

When ever user does not enter joining date instead of getting null value added into join_date column we end with exception on browser
Code Behind
    SqlCommand cmd1 = new SqlCommand("INSERT INTO emp(emp_name, join_date) VALUES(@emp_name1,@join_date1)", con1);

    cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar).Value = name.Text;
    cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime).Value = joindate.Text;

    Label1.Text = " Record added";



Make a check(if) for empty string and insert specific SqlDateTime.null value


cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar);
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime);

cmd1.Parameters["@emp_name1"].Value = name.Text;

System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;
if (joindate.Text == "") { cmd1.Parameters["@join_date1"].Value = getDate; }
else { cmd1.Parameters["@join_date1"].Value = joindate.Text; }



cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar)
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime)
cmd1.Parameters("@emp_name1").Value = name.Text

Dim getDate As SqlDateTime
getDate = SqlDateTime.Null

If joindate.Text = "" Then
   cmd1.Parameters("@join_date1").Value = getDate
   cmd1.Parameters("@join_date1").Value = joindate.Text
End If


Hope this helps!!


Comments (13)

  1. jagdish says:


    when I applied above solution got this error

    Cast from type ‘SqlDateTime’ to type ‘Date’ is not valid.

  2. Kostas says:

    Thank you! I was looking all over to find a way to insert Null in a datetime column.


  3. sudhir says:

    when i applied the above solution ,its not working

  4. Sandeep Shakya says:

    Before this code i had tried so many code but which is not working fine but after some changes this is really working fine.


  5. harsh says:

    i’m using dataset and adding record row by row in datatable

    of dataset and  then updating database.

    i’m getting error while adding null value to field of type datetime.

    my code look like…

    DataRow rowNew = m_dt_student.NewRow();

               rowNew["stud_id"] = m_NextStudId;

               rowNew["stud_name"] = txtbxStu_Name.Text;

               rowNew["stud_age"] = txtbxStu_Age.Text;

               rowNew["start_date"] = txtbxDate1.Text;

               //rowNew["end_date"] = txtbxDate2.Text;

               sqldatenull = SqlDateTime.Null;

               if (txtbxDate2.Text == "")


                   //txtbxDate2.Text = sqldatenull;

                   rowNew["end_date"] = Convert.ToDateTime(sqldatenull);


               rowNew["end_date"] = txtbxDate2.Text;


               SqlCommandBuilder cmdcon = new SqlCommandBuilder(adapter);


  6. Anbarasu says:

    Really Useful and Thanks a lot

  7. Jas says:


    Why you are doing this, you are bound to get error :

     rowNew["end_date"] = Convert.ToDateTime(sqldatenull);

    Try this

     rowNew["end_date"] = sqldatenull;



  8. 話題の小向美奈子ストリップを隠し撮り!入念なボディチェックをすり抜けて超小型カメラで撮影した神動画がアップ中!期間限定配信の衝撃的映像を見逃すな

  9. Bartek says:

    Wouldn't it be easier to make the date a nullable type?

  10. Fatima says:

    Thankssssssssssssssssssssssssssssss :))))

  11. kh says:

    set datatype allow nul and


  12. nilesh says:

    Cast from type 'SqlDateTime' to type 'Date' is not valid.  

    please add namespace

    using System.Data.SqlTypes;

Skip to main content