Insert NULL DateTime with SQL Server stored procedure from C#

Question

I have written a SQL Server stored procedure that includes a DateTime parameter. This parameter could be null or contain a valid date value.

So the definition of my variable in my procedure is below:

@piPurchase_Date DATETIME = NULL

From C# I add the parameter as follows:

SqlParameter parameter = new SqlParameter("@piPurchase_Date", SqlDbType.DateTime);
parameter.Value=datePurchaseIssueDate.Checked ? datePurchaseIssueDate.Value : SqlDateTime.Null;

So far all seems OK but when I call the Update method on the DataAdaptor I receive the following error:

Failed to convert parameter value from a SqlDateTime to a DateTime.

Any ideas?

I'm using SQL Server 2008 and C#4.0

Thanks in advance

I think you can use String.Empty or "" and this is converted to a NULL DateTime value. — MoonKnight
– MoonKnight, Commented Jun 19, 2012 at 14:19
have you just tried passing in null paramter.Value = datePurchaseIssueDate.Checked ? (DateTime?) datePurchaseIssueDate.Value : null; — Bob Vale
– Bob Vale, Commented Jun 19, 2012 at 14:20
You might want to try replacing the null section with DbNull.Value — Mike Perrenoud
– Mike Perrenoud, Commented Jun 19, 2012 at 14:22

marc_s · Accepted Answer · 2012-06-19 14:27:39Z

3

Yes - use DBNull.Value in C#:

SqlParameter parameter = new SqlParameter("@piPurchase_Date", SqlDbType.DateTime);
parameter.Value = datePurchaseIssueDate.Checked ? datePurchaseIssueDate.Value : DBNull.Value;

to pass a NULL to a SQL Server stored procedure

edited Jun 19, 2012 at 14:27

answered Jun 19, 2012 at 14:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mko Over a year ago

this would generate error Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DateTime' and 'System.DBNull'

Collectives™ on Stack Overflow

Insert NULL DateTime with SQL Server stored procedure from C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related