Dave Burke - A freelance .NET Developer specializing in Online Communities

A freelance .NET Developer

More on handling SQL null dates


Null SQL date values have been yankin' my nose hairs out of place lately, I think primarily because I'm using business objects more extensively which requires more value handling.  I'm going to say that the following is an Insider's TIP for handling null dates until someone tells me I'm full of crap, which inevitably seems to happen with Insider Tips.

I want to display "N/A" if the date from my business object class property is null.  '1/1/0001' continued to display as I went through

if (datein.Year == 1)..... if (datein.Year == null)....  if (datein.Year.ToString() = '1/1/0001').... if (datein.Year == System.DBNull.Value)

Then I went with the following, which seems to do the trick.  Seems if I compare the Year of a null value to a date that I know is less than any other date in the system that I'll achieve the desired results.

public string NAitDate(System.DateTime datein)
{
 if (datein.Year < 1950)
  return "N/A";
 else
  return datein.ToShortDateString();
}

 

Posted: Apr 28 2004, 03:45 PM by daveburke | with 11 comment(s)
Filed under:

Comments

Jerry Pisk said:

I keep wondering about how much you Dave actually know. You need to handle Null values before you convert them to value types, don't you know that value types cannot be null? And even then, the value you're getting when you convert NULL is DateTime.MinValue, so your check shouldn't be if the year is less than 1950 but if the DateTime value is the minimum value possible. Still, you should check for nulls BEFORE you convert to value type or use SqlDateTime, which is a reference type and can handle NULL values.
# April 28, 2004 4:42 PM

Dave Burke said:

Jerry, Shruggin' my shoulders. Don't know what to tell ya, dude. I know a lot about some things; not so much about others, like us all. I'm apparently light on under-the-covers info regarding null, value types, and reference types. But put a bunch of developers in a room and bring up the subject, there probably ain't a whole lot of fighting for floor time to get peoples' opinions in, I'm betting. You're right. I'm seeing that this is required reading.

Regarding checking for nulls BEFORE converting to value type or use SqlDateTime, you're advocating checking for null at the time of assigning a business property value? Say if I assign the bizobject datetime property from a database datareader, once assigned, I'm doing the checking for null, and thus the appearance of 1950.

Thanks!
# April 28, 2004 4:57 PM

Jon Galloway said:

Well, I think you're a genius. This is a simple way to check for null and MinValue at once. Maybe offensive from a purist point of view, but definitely a practical solution to an annoying problem. I wish DataTime's were nullable - null means more than 1/1/0001, which is still a magic number in my book.
# April 28, 2004 7:01 PM

Dave Burke said:

Jon, Funny! I just tested Jerry's [if (datein == DateTime.MinValue)] suggestion, which worked as well. There's a lot to this whole NULL thing. Thanks for sharing your thoughts!

# April 28, 2004 7:32 PM

Jerry Pisk said:

Dave, if you have a business object to hold your values then you're right, no checking should be there. But you should design your object to hold all possible values, if you have a database column that can hold NULL values then you need to make sure your business object can hold those as well. In this case, use SqlDateTime.

I do agree with Jon, DateTime should've been a class so it can be null. Pretty much everything should've been except basic types - characters and numbers. There should've been no structs in CLR. The performance gain is not worth the productivity loss.
# April 28, 2004 7:57 PM

Dave Burke said:

Thanks, Jerry, for both of your comments. I had not considered ensuring that the business object can hold nulls as well. That makes good sense. I will be investigating how SqlDateTime plays a role in this as well. Regards!
# April 28, 2004 9:24 PM

Jerry Pisk said:

No problem. I've ran into a very similar problem before - I was using Struts and designing all these strongly typed objects to collect user input. And after while I realized it's just pointless when it can't hold all the possible input values so I had to redesign all those objects to simply use strings to hold values, since the end user can pretty much enter anything. The lesson learned - you need to design your objects to hold all possible values, not just the ones you expect. If a database column allows NULL values then your object needs to be able to handle them, if you're population an object with user input on an HTML form (or anything that runs on the server) it needs to be able to hold everything that the protocol allows (Unicode strings in case of HTML) since it's so easy to bypass any client side checking.
# April 29, 2004 2:54 AM

Dave Burke said:

Jerry, Your input (not a pun :-) has been very valuable. Talk to you later!
# April 29, 2004 8:20 AM

David Knott said:

Hi
Why not create a NullableDateTime wrapper class around the DateTime struct that will encapsulate the necessary business logic to handle nulls ?

Nice site.
# May 14, 2004 6:01 AM

Dave Burke said:

David, I'm going to have to think about that. Wrapper class, hmmm. Thanks very much for your thoughts on this!
# May 14, 2004 7:32 AM

Nagendra82singh said:

i want ot assign  null value of date field  by default

# September 10, 2008 3:31 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)