Date stored in DataBase as NULL is returned as 01/01/1900 and not as DateTime.MinValue

Question

I have a logic that does the following:

string myData =  Convert.ToDateTime(Info.ClosedDate) == DateTime.MinValue ? String.Empty : "(" + Info.ClosedDate + ")";

It should return ClosedDate in parenthesis if it is not stored as NULL in the database. Otherwise it should return an empty string.

The value of Convert.ToDateTime(Info.ClosedDate) is 01/01/1900 and the value of DateTime.MinValue is 1/1/0001 12:00:00 AM

So, the condition will never return String.Empty

Currently the field in the object is represent as :

public string ClosedDate
{
    get { return _ClosedDate; }
    set { _ClosedDate = value; }
}

What is the best solution?

Answer 1

Don't store or model DateTime types as string.

This applies to the data store but also to your models in the code. If it can be nullable in the database than model it as Nullable<DateTime> (alternate notation DateTime?) in your code. Only convert it to a string at the last possible moment, usually in the presentation layer. If you do that there is no need to do any type conversion when reading/writing to the data store. You also avoid ambiguity on the value interpretation (like what value is null or is it localized as "dd/MM/yyyy" or "MM/dd/yyyy").

private DateTime? _ClosedDate;
public DateTime? ClosedDate
{
    get { return _ClosedDate; }
    set { _ClosedDate = value; }
}

side note: the above could also be modeled as an auto property but I did not do so because it is not clear how the field is used in the model

If you are not sure how to pass a DateTime instance to an ADO.NET query as a parameter in it's native format please review this previous question/answer: How can I add user-supplied input to an SQL statement?

Answer 2

You can compare against SqlDateTime.MinValue

Convert.ToDateTime(Info.ClosedDate) == SqlDateTime.MinValue? String.Empty : "(" + Info.ClosedDate + ")";

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqltypes.sqldatetime.minvalue?view=netframework-4.7.2

Answer 3

Although its best to use DateTime as type for dates, your code should like this:

public DateTime ClosedDate
{
    get; set;
}

You can also use auto properties like this or private set upto the requirements.

public DateTime ClosedDate 
{
   get;
   set
   {
     Date = DateTime.Now;
   }
}

For string, you can use DateTime.Parse or DateTime.ParseExact instead of Convert.ToDateTime.

If value is null then Parse or ParseExact returns ArgumentNullException and same way if value contains some invalid date format then it returns FormatException.

var convertedDate = DateTime.ParseExact(dateTime, "yyyyMMdd", CultureInfo.InvariantCulture);