Return NULL datetime value from T-SQL into C# DataTable ==> DataRow instead of empty string

Question

This value (row[10]) is coming from a DataRow object, from a SQL result set in T-SQL. I believe it has a type "object". In my database, I have a value of NULL for this field for this particular record, but it's returning an empty string in my result set, not a null value. I'd like to fix the root problem and have my result set return NULL instead of an empty string, but if that's not possible, making this code more efficient would be just fine--meaning the 1st snippet, since it works for all 3 cases.

This works when row[10].ToString() is equal to an empty string, null or a DateTime format, but I'd like to shorten it. This is my workaround for now.

        string temp0 = row[10].ToString();
        DateTime? temp = null;
        if (temp0 == "")
        {
            temp0 = null;
        }
        if (temp0 != null)
        {
            temp = DateTime.Parse(temp0);
        }

        d.date_migrate_prod = temp == null ? null : temp;

This works for a null datetime value, an actual datetime value, but not when row[10] is equal to an empty string.

        DateTime? temp = DateTime.Parse(row[10].ToString());
        d.date_migrate_prod = temp == null ? null : temp;

Try checking `DBNull`. http://msdn.microsoft.com/en-us/library/system.dbnull.aspx — Dave Zych, Oct 21 '12 at 17:26
I suppose you know that there is `DBNull` which means null values in the field whereas `null` is for reference type variables in c# which isn't assigned any value. Does `row[10]` refer to a field of type `varchar`? — shahkalpesh, Oct 21 '12 at 17:37

score 2 · Answer 1 · answered Sep 05 '14 at 07:01

The correct way to do this is:

 DateTime? temp = null; //this is fine
 var indexOfYourColumn = reader.GetOrdinal("columnName");//not necessary but makes iterating faster
 while (reader.Read())
 {
       temp = reader[indexOfYourColumn] != DBNull.Value ? (DateTime?) null : DateTime.Parse(reader[indexOfYourColumn].ToString())
 }

Why? You can't do .ToString() on a null. You then need to cast like to like. So nullable DateTime to nullable DateTime.

This was asked long time ago. But the accepted answer is not correct.

paparazzo · Answer 2 · 2012-10-21T19:16:29.603

1

An answer to the stated question.

string temp0 = row[10].ToString();
DateTime?;
if (string.IsNullOrEmpty(temp0)) 
{
    temp = null;
}
else
{
    temp = DateTime.Parse(temp0);
}

Could use DateTimeTryParse.
Can pass a null but it will parse to 1/1/0001

edited Oct 21 '12 at 19:16

answered Oct 21 '12 at 19:07

paparazzo

44,497
23
105
176

score 1 · Accepted Answer · edited Feb 20 '14 at 00:06

1

The following should work as a shortcut:

DateTime? temp = String.IsNullOrEmpty(row[10].ToString())? null : DateTime.Parse(temp0);

edited Feb 20 '14 at 00:06

JClaspill

1,725
19
29

answered Oct 21 '12 at 19:23

thealghabban

448
2
4

I had to cast the last piece of the statement before it would work. d.date_migrate_prod = String.IsNullOrEmpty(row[10].ToString()) ? null : (DateTime?)DateTime.Parse(row[10].ToString()); ...... I still haven't figured out how to get System.DBNull to work yet – JustBeingHelpful Oct 22 '12 at 04:44
For now, this works (in my comment), so I'm marking it as the answer. If anyone wants to help with the root cause, I'll test your answer out. – JustBeingHelpful Oct 23 '12 at 00:29

Farfarak · Answer 4 · 2012-10-23T08:11:58.830

0

Try:

DateTime? localvariable 
      = row[10] == Convert.DBNull ? null : Convert.ToDateTime(row[10].ToString())

edited Oct 23 '12 at 08:11

answered Oct 22 '12 at 14:31

Farfarak

1,497
1
8
8

'@010001100110000101110010011010' RE ANSWER: Try localvariable = row[10] == Convert.DBNull ? null : row[10].ToString() ....... GETTING ERROR: Cannot implicitly convert type 'string to 'System.DateTime?' – JustBeingHelpful Oct 23 '12 at 00:27
My bad: DateTime? localvariable = row[10] == Convert.DBNull ? null : Convert.ToDateTime(row[10].ToString()) – Farfarak Oct 23 '12 at 08:06

Return NULL datetime value from T-SQL into C# DataTable ==> DataRow instead of empty string

4 Answers4

Linked