How to get nullable DateTime out of the database

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];

Solution 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.

Solution 2:

I recently found this trick, it's simple:

DateTime? dt = sqldatareader[0] as DateTime?;

Solution 3:

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]);