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]
	(
	@WO_NUMBER AS VARCHAR(50),
	@COMP_DT AS SMALLDATETIME
	)AS

	INSERT INTO dbo.FAULT
		(
		WO_NUMBER,
		COMP_DT
		)
	VALUES
		(
		@WO_NUMBER,
		@COMP_DT
		)
	END

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);
    dbCon.Open();
    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);
    }
    else
    {
        dbCom.Parameters.AddWithValue("@COMP_DT", DBNull.Value);
    }

    //Execute the Stored Proc
    dbCom.ExecuteNonQuery();
    dbCon.Close();
}

One Comment

  1. Jeywin said:

    Nice Simple & Quick solution

    April 24, 2015
    Reply

Leave a Reply