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();
}
Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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