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, Dec 29 '11 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, Dec 29 '11 at 17:20
@mjd79 It works with `SqlDbCommand`, maybe just not with `DbCommand`. — Yuck, Dec 29 '11 at 17:51

score 13 · Accepted Answer · edited Dec 29 '11 at 14:40

13

Try the null coalescing operator:

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

edited Dec 29 '11 at 14:40

Joe

80,724
18
127
145

answered Dec 29 '11 at 14:37

matt

9,113
3
44
46

4

I'm a bit surprised that this is not handled by the db provider. – George Mauer Dec 29 '11 at 14:42

score 0 · Answer 2 · edited Dec 29 '11 at 14:48

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 '11 at 14:48

Surjit Samra

4,614
1
26
36

answered Dec 29 '11 at 14:43

Brijesh Mishra

2,738
1
21
36

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

2 Answers2

Linked