1

I'm performing a simple SELECT statement and I'm trying to return the value from a DateTime column from a SQL Server 2012 table. The problem is that when I return a NULL DateTime value I don't know how to manage this with my code below. 

dtTrainingEnd = (DateTime)reader["TrainingEnd"];

I've searched for the last few days on an answer and cannot find something that will help me. I've found similar posts but still I cannot figure out how they can help me. Can you please explain how I can check to see if the datetime value returned from the database is NULL?

SqlConnection connRead = new SqlConnection(connReadString);
SqlCommand comm = new SqlCommand();
SqlDataReader reader;
string sql;

DateTime dtTrainingEnd = DateTime.Now;
int iTrainingSwipeID = 123;

sql = "SELECT TrainingEnd FROM TrainingSwipe WHERE TrainingSwipeID = " + iTrainingSwipeID;

comm.CommandText = sql;
comm.CommandType = CommandType.Text;
comm.Connection = connRead;

connRead.Open();
reader = comm.ExecuteReader();

while (reader.Read())
{
    dtTrainingEnd = (DateTime)reader["TrainingEnd"];
}
connRead.Close();
Andy
  • 383
  • 1
  • 6
  • 23
  • A note on the DateTime type, DateTime is a (struct) value type, when retrieving a null value it would set itself to the default value. The default value of this type is '01/01/0001'. – M.B. Mar 25 '19 at 13:46
  • If the database allows nulls, so should do your code. Consider using nullable types where those columns are involved. – Cleptus Mar 25 '19 at 13:48

2 Answers2

3

If it might be null, you could use a nullable type... in this case, a DateTime? type:

while (reader.Read())
{
    dtTrainingEnd = ((DateTime?)reader["TrainingEnd"]) ?? some_default_date;
}
connRead.Close();

Or just test for null if you'd rather do that:

while (reader.Read())
{
    var endDate = reader["TrainingEnd"];
    dtTrainingEnd = (endDate == null) ? some_default_date : (DateTime)endDate;
}
connRead.Close();

In both cases above, I assumed you want dtTrainingEnd to contain something if the date is NULL in the database, so some_default_date is some default DateTime.

Or if you want to leave dtTrainingEnd alone if the value is NULL, then just don't set it in that case:

while (reader.Read())
{
    if ((reader["TrainingEnd"]) != null)
        dtTrainingEnd = (DateTime)reader["TrainingEnd"];
}
connRead.Close();

*** Depending on how you connect to your db, you may have to replace null with DBNull.Value

Grant Winney
  • 65,241
  • 13
  • 115
  • 165
  • 3
    a broader pattern could be `reader["field"] as DateTime? ?? some_default_date`; this will be useful for handling the same scenario with DataTables, as `null` become `DBNull.Value` – Rubens Farias Mar 25 '19 at 13:42
  • @Grant, In your second example what would be saved into dtTrainingEnd if the database value is NULL? In other words what would be the if statement to see if the datetime is NULL or not? Sorry, I'm totally ignorant on this topic. – Andy Mar 25 '19 at 13:44
  • @Grant, Ok, I think I got it. Thank you very much for your patience and help. :D – Andy Mar 25 '19 at 13:56
  • @Grant, when I put the code in I get the error "System.IndexOutOfRangeException: TrainingEnd" on the if statement "if ((reader["TrainingEnd"]) != null)". Any ideas? I can't believe this is so difficult in c# to see if a datetime is null... :( – Andy Mar 25 '19 at 14:04
  • Arrgh....my fault @Grant. Got it now. Your code works perfectly. Thank you again!!! – Andy Mar 25 '19 at 14:11
  • 1
    @Grant, for some reason for me the check "== null" was returning not null for all records from the database that were not null. So, a little searching found that for me "== DBNull.Value" did the trick. Not sure what the difference is but I thought I'd mention it in case someone else had the same problem. – Andy Mar 25 '19 at 17:18
1

With SQL you can do SELECT Coalesce(TrainingEnd,0) and if it is null, you would have a 1900-01-01 date...

Dominic
  • 194
  • 4