Menu
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];
573
DateTime itself is a value type. It cannot be null.
Using a DateTime column in an SQL table is quite common. Using it in . Net has one limitation – DateTime cannot be null as it is a struct and not a class.
A null value in a relational database is used when the value in a column is unknown or missing. A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types).
The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
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.
149
I recently found this trick, it's simple:
var dt = sqldatareader[0] as DateTime?;
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])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
