How to get nullable DateTime out of the database

c# sql-server datetime nullable sqldatareader
pikachu picture pikachu · Feb 29, 2012 · Viewed 23k times · Source

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

Dour High Arch picture Dour High Arch · Feb 29, 2012

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.

Related questions

Conversion of a datetime2 data type to a datetime data type results out-of-range value

I've got a datatable with 5 columns, where a row is being filled with data then saved to the database via a transaction. While saving, an error is returned: The conversion of a datetime2 data type to a datetime data type …

Read More
Using DateTime in a SqlParameter for Stored Procedure, format error

I'm trying to call a stored procedure (on a SQL 2005 server) from C#, .NET 2.0 using DateTime as a value to a SqlParameter. The SQL type in the stored procedure is 'datetime'. Executing the sproc from SQL Management Studio works fine. …

Read More
Linq to SQL DateTime values are local (Kind=Unspecified) - How do I make it UTC?

Isn't there a (simple) way to tell Linq To SQL classes that a particular DateTime property should be considered as UTC (i.e. having the Kind property of the DateTime type to be Utc by default), or is there a …

Read More