How to Convert a Nullable DateTime variable's null value to DbNull.Value

Question

I have a nullable DateTime Variable. And I want to write it to SQL DB. When i try to insert:

If the variable has value there is no problem.

But if it hasn't a value, insertion interrupting with an error.

I want to ask: How can we insert nullable DateTime to Sql via DbCommand Parameter?

(P.S. : Sql column is nullable too.)

DateTime? myDate = null;
DbCommand dbCommand = new DbCommand();
dbCommand.Parameters.Add("NullableSqlDateField", DbType.DateTime, myDate);

What's the error you get? I believe you can use null and DBNull.Value interchangeably. — Yuck
– Yuck, Commented Dec 29, 2011 at 14:48
@Yuck - I don't think you can use the two interchangeably, or at the very least, you can't with all database providers. You should use DBNull.Value when inserting null values into a database. There is a slight difference in semantics when dealing with null vs. DBNull.Value: null refers to an invalid object reference, whereas DBNull.Value refers to an unknown value in a database row. Seems like hair-splitting, but it's the best reasoning I can see for the difference. — matt
– matt, Commented Dec 29, 2011 at 17:20
@mjd79 It works with SqlDbCommand, maybe just not with DbCommand. — Yuck
– Yuck, Commented Dec 29, 2011 at 17:51

Joe · Accepted Answer · 2011-12-29 14:40:01Z

13

Try the null coalescing operator:

dbCommand.Parameters.Add("NullableSqlDateField", DbType.DateTime, (object) myDate ?? DbNull.Value);

edited Dec 29, 2011 at 14:40

Joe

82.8k18 gold badges130 silver badges147 bronze badges

answered Dec 29, 2011 at 14:37

matt

9,4923 gold badges46 silver badges47 bronze badges

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

1 Comment

George Mauer Over a year ago

I'm a bit surprised that this is not handled by the db provider.

Surjit Samra · Accepted Answer · 2011-12-29 14:48:29Z

0

try this

if(myDate.HasValue)
  dbCommand.Parameters.Add("NullableSqlDateField", DbType.DateTime, myDate.Value);
else
  dbCommand.Parameters.Add("NullableSqlDateField", DbType.DateTime, DbNull.Value);

edited Dec 29, 2011 at 14:48

Surjit Samra

4,6721 gold badge29 silver badges36 bronze badges

answered Dec 29, 2011 at 14:43

Brijesh Mishra

2,7581 gold badge21 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to Convert a Nullable DateTime variable's null value to DbNull.Value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related