Hello Everyone and thanks for your help in advance. I am developing an application that utilizes a separate class library for data access to a SQL Server database. One of the tables has several fileds defined as DateTime. The data access layer exposes properties for each field in the table. In the instance of datetime fields, the class returns a property of date type. The problem occurs when one of the datetime fields is NULL, which results in a value of 12:00:00 AM being returned. I then tried the following code:

If Not IsDBNull(myclass.mydate) then
    myTextbox.Text = myClass.myDate
Else
    mytextbox.Text = ""
End If

However, it still returned the 12:00:00 AM. Is there any way around this problem other than returning a string value, that converting it is datetime functions are required? Any help would be greatly appreciated. Thanks.

Hi Hugh,

A DateTime value is not null when you have just declared but initialized it. So, DBNull will not return true.
One solution I have used so far is to initialize a new Date to DateTime.MinValue. Then in your code, you can check for that value. If the date you're comparing equals the MinValue, apparently you have a null date. For me this has always worked, because my business logic never had to use the MinValue (00:00:00.0000000, January 1, 0001): I realize this is a bit like the old skool 99999 markers, but IMO this solution is a bit more legitimate.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Wow, Imar. You lost me on this one. Are you saying that the dateTime is already initialized to 12:00:00 am once the read from the database occurs? If this is the case, couldn't I just check to see if only 12:00:00 am is returned. Or is it simply coming down to possibly storing the data as a string, the converting in the business logic when I need to do date comparisons?

As always, thanks for your help. Hope all is well with you.

Yeah, I am fine. Thank you.

How do you initialize your private (backing) fields for your public DateTime properties?

That's what I was referring to. I usually, do something like this (although I am sure there must be a better way):

private DateTime startDate = System.DateTime.MinValue;

public DateTime StartDate
{
  get
  {
     return startDate;
  }
}

// Class constructor
// Get stuff from database
if (!myDataReader.IsDBNull(0))
{
  startDate = myDataReader.GetDateTime(0);
}


Basically, here is what happens:

1. A new private variable is declared and initialized to the DateTime.MinValue
2. When the constructor is called, some code will retrieve the record from the database. If column 0 (the startDate) is not null in the database, the private variable gets the date from the column assigned, otherwise its value remains set to DateTime.MinValue.

Calling Code, (outside this class) can now use this property like this:

if (myObject.StartDate == DateTime.MinValue)
{
  // Consider it null
}
else
{
  // Not null.
}

I think the basis of this problem is that the DateTime is a value type, so this won't work:

DateTime myDate = null;

So, the MinValue is pretty much the same idea as this:

int MyInteger = -1;

where the MyInteger is initialized to a non-null value. This -1 value is then used to see whether the value has been set somewhere. This only works when -1 is not a valid normal value. The same applies to DateTime.MinValue.

Does this help?


Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Yes, that makes sense. So basically, whether you use MinValue or simply fitler the 12:00:00 am, your business logic must handle a test for something other than NULL. Correct?

Not sure what you mean with your last question.
Since a DateTime variable can't be initialized to null, I use MinValue to simulate null.
If the date still equals MinValue, its value hasn't been set somewhere, so it's considered null.

Is this what you mean?

And how do you initialize your datetime values to 12:00 am? What code are you using or where does this value appear?


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

To initialize the data I do the following:

Private _StartDate As Date

If Not IsDBNull(myReader("StartDate")) Then
    _StartDate = myReader("StartDate")
End If

Then, I set the property like this:

    ReadOnly Property StartDate() As Date
        Get
            Return _StartDate
        End Get
    End Property

So in my thinking, when I reference the myClass.StartDate property, it should return a NULL. But it seems I am wrong.

That is what Imar is saying. Date is a value type not a reference type. A value type can not be null/Nothing. A reference type CAN be null/Nothing.

Reference types are variables that hold a memory address that points to a place on the .NET runtime's heap. That variable can point to nowhere (how technically, I don't know) and is therefore null/Nothing.

A value type however, is different. When you instantiate a value type (like an Integer) the runtime actually allocates memory to hold whatever could be in that variable. This variable lives on the runtime's stack. Something must be in that variable. In the case of an Integer that value is -1.

Example:
If you do this: Private nMyInt As Integer
Is nMyInt null/Nothing? No, it's -1. Just like an integer, Date is a value type. It will always have SOME value. Booleans do this too. They are always defaulted to False.

Imar's technique is to set the date variable to something you know you will never use (like DateTime.MinValue). Then you test for that instead of testing for null/Nothing. MinValue acts as a substitute for null/Nothing because a Date value cannot BE null/Nothing.

Peter
------------------------------------------------------
Work smarter, not harder.

Okay. This is starting to make sense. So using MinValue Vs. simply using logic to detect a 12:00:00 am return value is simply personal preference. I guess the MinValue is a more absolute situation.

Couldn't agree with you more, Peter,

Hugh: In addition to all this, a DateTime which isn't initialized, is considered "undefined" or "unassigned". This is something completely different from null.

Like usual, there are may ways leading to Rome. Using MinValue might be just as good as any other. Another solution would be to use an additional Property in your class, like DateIsSet. When the date has been set, this property returns true, otherwise it returns false. Personally, I prefer to MinValue solution, until I start that little History project....

What I still don't get is how do you set your initial DateTime value to 12:00. I'm still missing that part and the part where you compare against 12:00....

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.