best solution for Null DateTime value

Question

I couldn't find best solution when DateTime value is NULL.

I use this techinque When binding;

_ACTIVATION_DATE = dt.Rows[0]["ACTIVATION_DATE"] == DBNull.Value ? new DateTime(1970, 12, 30) : (DateTime?)dt.Rows[0]["ACTIVATION_DATE"];

When Inserting;

public void Insert()
{
    string ad="";
    string dd="";

    if (ACTIVATION_DATE == null)
        ad = "null";
    else
        ad = "'" + ACTIVATION_DATE + "'";
    if (DEACTIVATION_DATE == null)
        dd = "null";
    else
        dd = "'" +DEACTIVATION_DATE +"'";

    string sSQL = "INSERT INTO LINE_INFO (ACTIVATION_DATE,DEACTIVATION_DATE,STATUS,PO,NOTES) VALUES (" + ad + "," + dd + "," + _STATUS.ToString() + "," + _PO.ToString() + ",'" + _NOTES.ToString() + "');SELECT @@IDENTITY AS LASTID";
    }

Variables;

DateTime? ACTIVATION_DATE;
DateTime? DEACTIVATION_DATE;

What is the smart way to handle Null DateTime values?

When I find the solution I will write an article about this topic.

Answer 1

DateTime? implies you're using a Nullable<DateTime> to store the value - so why not just use .HasValue and .Value?

Answer 2

Why are you using new DateTime(1970, 12, 30) when you're already using a nullable DateTime? The whole point of nullable value types is that you don't need magic values like that.

I would possibly use:

_ACTIVATION_DATE = dt.Rows[0]["ACTIVATION_DATE"] as DateTime?;

That will automatically use the null value for any non-DateTime value. Of course, that means you'll get a null value instead of an exception if you accidentally have an integer or something like that. Alternatively:

object tmp = dt.Rows[0]["ACTIVATION_DATE"];
_ACTIVATION_DATE = tmp is DbNull ? null : (DateTime?) tmp;

Then for the insert statement, do not include values directly in your SQL. Use a parameterized insert statement, and then you can just use a null DateTime? value to insert a null value. No need to mess around with string formats.

Answer 3

You shouldn't insert values directly because you create possibility to SQL injections. Instead you should use parameterized queries:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "INSERT INTO table1 (column1, column2) VALUES (@param1, @param2)";

    command.Parameters.Add("@param1", SqlDbType.DateTime).Value = 
        DateTime.TryParse(txtDate.Text, out d) ?
            (object)d :
            DBNull.Value // inserting NULL
    ...

    connection.Open();
    command.ExecuteNonQuery();
}

When binding:

object date = reader["date"]; // NULL or '2010-08-26'
txtDate.Text = Convert.IsDBNull(date) ? (DateTime)date : String.Empty;
// or
txtDate.Text = (reader["date"] as DateTime? ?? String.Empty).ToString();

Answer 4

The null handling i most cases depend on the business needs, in your case seams that You need to store some value, but the information that date is null sometimes is useful. Because something has not happened yet. So its hard to determine some good approach to null date handling.

In Your case what it could be done i to replace that new DateTime(1970, 12, 30) with a static const field called default date.

public const DateTime DEFAULT_DATE = new DateTime(1970,12,30);


public void Insert()
{
  string activationDate = "null";
  string deactivationDate= "null";

  if (ACTIVATION_DATE != null) {
   ad = string.format("'{0}'",ACTIVATION_DATE); //Hire should be some date format used
  }

  if (DEACTIVATION_DATE != null) {
   ad = string.format("'{0}'",DEACTIVATION_DATE); //Hire should be some date format used
  }


   string sSQL = string.format("INSERT INTO LINE_INFO (ACTIVATION_DATE,DEACTIVATION_DATE,STATUS,PO,NOTES) VALUES ({0},{1},{2},{3},'{4}');SELECT @@IDENTITY AS LASTID",activationDate ,deactivationDate ,_STATUS,_PO,_NOTES);

Ps. You should not use this type of statement creation instead of this you should use SqlCommand and paramteres