How to get nullable DateTime out of the database

Question

My SQL Server database contains nullable DateTime values. How can I convert them to a nullable DateTime object in my application in C#?

This is what I would think it would look like, but it doesn't:

DateTime? dt = (DateTime?) sqldatareader[0];

Answer 1

A SQL null is not the same as a .NET null; you have to compare against System.DBNull.Value:

object sqlDateTime = sqldatareader[0];
DateTime? dt = (sqlDateTime == System.DBNull.Value)
    ? (DateTime?)null
    : Convert.ToDateTime(sqlDateTime);

In answer to your comment, the data type of the Item property of a DataReader is that of the underlying database type. It could be System.Data.SqlTypes.SqlDateTime for a non-null SQL Server database, or System.DBNull for a null column, or System.Data.Odbc.OdbcTypes.SmallDateTime for an ODBC database, or really just about anything. The only thing you can rely on is that it is of type object.

This is also why I suggest using Convert.ToDateTime() instead of type coercion to DateTime. There is no guarantee a ODBC or whatever date column can be coerced to a .NET DateTime. I note your comment specifies a "sqldatareader", and a SQL Server System.Data.SqlTypes.SqlDateTime can indeed be coerced to a System.DateTime, but your original question did not tell us that.

For more information on using DataReaders, consult MSDN.

Answer 2

I recently found this trick, it's simple:

var dt = sqldatareader[0] as DateTime?;

Answer 3

how about creating helper method

private static DateTime? MyDateConverter(object o)
{
    return (o == DBNull.Value || o == null) ? (DateTime?)null : Convert.ToDateTime(o);
}

Usage

MyDateConverter(sqldatareader[0])

Answer 4

You need to check if the value "is DBNull" rather than just null. I posted a small helper class on my blog: http://improve.dk/archive/2007/10/08/handling-dbnulls.aspx

Once you implement the class, you use it like this:

DateTime? dt = DBConvert.To<datetime?>(sqldatareader[0]);

Answer 5

A while ago, I wrote a bunch of extension methods for a DataRow to do just this sort of downcasting...because I hate writing repetitive drivel. Usage is simple:

foreach( DataRow dr in someDataTable )
{
  DateTime? dt = dr.CastAsDateTimeNullable( "lastUpdated" ) ;
  int       id = dr.CastAsInt( "transactionID" ) ;
  // etc.
}

Here's the piece for DateTime values. Adding implementation for for other data types should be pretty trivial. Wouldn't be difficult to do the same sort of thing to a DataReader if one was so inclined.

I tried to come up with generic methods, but limitations in the way generics are done made it difficult or impossible to do and still get the behavior I wanted (e.g., null values rather than default(T) — getting default values for SQL NULLs that would make differentiating between 0 and null...difficult).

public static class DataRowExtensions
{

  #region downcast to DateTime

  public static DateTime CastAsDateTime( this DataRow row , int index )
  {
    return toDateTime( row[index] ) ;
  }
  public static DateTime CastAsDateTime( this DataRow row , string columnName )
  {
    return toDateTime( row[columnName] ) ;
  }

  public static DateTime? CastAsDateTimeNullable( this DataRow row , int index )
  {
    return toDateTimeNullable( row[index] );
  }
  public static DateTime? CastAsDateTimeNullable( this DataRow row , string columnName )
  {
    return toDateTimeNullable( row[columnName] ) ;
  }

  #region conversion helpers

  private static DateTime toDateTime( object o )
  {
    DateTime value = (DateTime)o;
    return value;
  }

  private static DateTime? toDateTimeNullable( object o )
  {
    bool  hasValue = !( o is DBNull );
    DateTime? value    = ( hasValue ? (DateTime?) o : (DateTime?) null ) ;
    return value;
  }

  #endregion

  #endregion downcast to DateTime

  // ... other implementations elided .. for brevity

}

Answer 6

DateTime? dt = null;

if (sqldatareader[0] != System.DbNull.Value)
{
    dt = (DateTime)sqldatareader[0];
}