Nullable DateTime and the Database

Question

I've got a nullable datetime object in C#.

DateTime? StartDate = new DateTime();

I then check a user submitted value to determine if a date is applicable to this record:

if (Complete == "N/A")
{
    StartDate = null;
}

Now I come to my query which may or may not be inserting a null datetime:

using (SqlCommand command = new SqlCommand(updateSql, db))
{
    command.Parameters.Add("@Date_Started", SqlDbType.DateTime).Value = StartDate;
}

As you'd probably expect if the start date is null then I get an error that the type is not correct. What's the best way to proceed from here?

I have considered checking startdate is null but am not sure how to do that when the type is nullable.

As an aside, you can easily check if a nullable type is null in two ways. 1) `!StartDate.HasValue`, or 2) Simply `StartDate == null` — Ray, Feb 01 '12 at 16:19

Roy Goode · Accepted Answer · 2012-02-01T16:23:10.210

19

This will pass a database NULL value as the parameter if StartDate is null:

using (SqlCommand command = new SqlCommand(updateSql, db))
{
    command.Parameters.Add("@Date_Started", SqlDbType.DateTime).Value = (object)StartDate ?? DBNull.Value;
}

edited Feb 01 '12 at 16:23

answered Feb 01 '12 at 16:11

Roy Goode

2,940
20
22

This is what I was attempting, didn't know of this shorthand syntax though! :) – deed02392 Feb 01 '12 at 16:16
Error with this too: Operator '??' cannot be applied to operands of type system.datetime? and system.dbnull. – deed02392 Feb 01 '12 at 16:20
1

@deed02392 fixed with a simple cast to object – Roy Goode Feb 01 '12 at 16:22

score 2 · Answer 2 · answered Feb 01 '12 at 16:12

using (SqlCommand command = new SqlCommand(updateSql, db))
{
    if (StartDate.HasValue())
        command.Parameters.Add("@Date_Started", SqlDbType.DateTime).Value
            = StartDate;
    else
        command.Parameters.Add("@Date_Started", SqlDbType.DateTime).Value 
            = DBNull.Value;
}

Nullable DateTime and the Database

2 Answers2

Linked