Null Dates in SQL Stored Procedure called from C# – Conversion Error DateTime2 DateTime

One issue with calling stored procedures from C# is with null date parameters. If you try to pass an unassigned DateTime type to the parameter you will receive an error regarding conversion of DateTime2 to DateTime data type.

One way around this is to use the Nullable DateTime in C#

Lets assume we have the following table (dbo.Fault):

WO_NUMBER nvarchar(50) not null
COMP_DT smalldatetime null

And an Insert Stored Procedure

CREATE PROC [dbo].[up_add_fault]


If we try to send an unassigned DateTime varible to the @COMP_DT parameter we will get the error, as an unassigned DateTime value is not null.

So we need to declare our variable as a nullable DateTime type. In C# we use DateTime? to do this. We can then test the HasValue of the variable, and depending on the result either pass the variable, or pass DBNull.Value

private DateTime? compDate;
private string woNumber;

//do something to assign values to the varibles here...
//call saveFault

saveFault(woNumber, compDate)

private void saveFault(string woNumber, DateTime? compDate)
    //Set up database connection & stored proc
    OleDbConnection dbCon = new OleDbConnection(dbConnectionString);
    string dbSQL = "dbo.up_add_fault";
    OleDbCommand dbCom = new OleDbCommand(dbSQL, dbCon);
    dbCom.CommandType = CommandType.StoredProcedure;

    //add the WO_NUMBER parameter
    dbCom.Parameters.AddWithValue("@WO_NUMBER", woNumber);

    //test the compDate to see if it has a value - if yes use it else use use DBNull
    if (compDate.HasValue)
        dbCom.Parameters.AddWithValue("@COMP_DT", compDate);
        dbCom.Parameters.AddWithValue("@COMP_DT", DBNull.Value);

    //Execute the Stored Proc
This entry was posted in Programming and tagged , , , , , , . Bookmark the permalink.

One Response to Null Dates in SQL Stored Procedure called from C# – Conversion Error DateTime2 DateTime

  1. Jeywin says:

    Nice Simple & Quick solution

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s