Fog Creek Software
Discussion Board




Date cannot be NULL in .NET

What was Microsoft thinking with C# structs? You cannot set a struct variable to null! I've read about a couple work arounds for this and it's insane. Use DBDate instead or build some kind of wrapper which hides the date in an object (which just moves the problem down one level).

In my sysytem I just ended up keeping the data as a string and converting it when needed (a lot) to see if it's a valid date or not. Lots of unecessary complicated coding. VB actually has an advantage here you could use a Variant variable and Nothing to help out. In C# you are screwed.

Annoyed
Friday, January 23, 2004

You could use a nullable Date/Time such as System.Data.SqlTypes.SqlDateTime, or the open source version at http://nullabletypes.sourceforge.net/.

Alternatively better than keeping it in a string would be to box it in an object, something like:

DateTime dt = whatever;
object nullableDateTime;
...
nullableDateTime = dt;
...
if (nullableDateTime == null)
{
    dt = (DateTime) nullableDateTime;
}
else
{
    // handle null value
}

Joe
Friday, January 23, 2004

What Microsoft was thinking was something along the lines of "It would be useful to have aggregate types that are value types, implicitly copied when they are assigned, passed and returned, and that don't use any extra space and time for pointer indirection". Given that, the non-nullability follows immediately.

It's not clear whether they were *right* to think that, but it's not as if they arbitrarily decided to forbid setting a struct to null. The prohibition is a consequence of what structs are.

Gareth McCaughan
Friday, January 23, 2004

"VB actually has an advantage here you could use a Variant variable and Nothing to help out. In C# you are screwed."

C# has the object type which, with RTTI, could be considered the modern variant.

i.e.

System.Object myValue = (System.Object)System.DateTime.Now;

...

if (myValue != 0)
{
  // we are non-null
  if (myValue.GetType().FullName == "System.DateTime")
  {
      // it's a date!
      System.DateTime tomorrow = ((System.DateTime)(myValue)).Add(new System.TimeSpan(1,0,0,0,0));

  }
}

How is this any less powerful than a Variant? Better yet if you don't want the overhead of a nullable value you can use the base value type.

Dennis Forbes
Friday, January 23, 2004

you can always "roll your own" ...

http://hosca.com/rotor/datetime_8cs-source.html

replace the line :

public struct DateTime : IComparable, IFormattable, IConvertible

with

public class DateTime : IComparable, IFormattable, IConvertible

erhan hosca
Friday, January 23, 2004

In addition to the other good suggestions, you could just use a predefined "magic number" that represents a null value.  For example, you could use zero ticks, or "00:00:00.0000000, January 1, 0001," to represent a null value:

if (dt.Ticks == 0) {
    // the number is null
}
else  {
    // it isn't    
}

For code readibility/maintainability issues, you could define the magic number with a constant, like "dtNull."

Robert Jacobson
Friday, January 23, 2004

> For code readibility/maintainability issues, you could
> define the magic number with a constant, like "dtNull."

If so, use DateTime.MinValue (which is the default value and unlikely to represent a real date) as your magic number.


Friday, January 23, 2004

<If so, use DateTime.MinValue ...>

Yup, DateTime.MinValue = 0 ticks = "00:00:00.0000000, January 1, 0001."  Since it's the default value, the advantage of using this is that any unitialized DateTime will automatically be "null."  IMO, it would be better to declare a special constant to represent this value, just to make the code a bit more understandable.

Robert Jacobson
Friday, January 23, 2004

> For code readibility/maintainability issues, you could
> define the magic number with a constant, like "dtNull."

I'm not sure it applies to the current discussion, but aren't 'magic numbers' what caused all sorts of grief for those companies where 9/9/99 was used as a 'magic number' meaning 'null'?

Ron Porter
Friday, January 23, 2004

< I'm not sure it applies to the current discussion... >

Absolutely.  There's always the danger of a collision between the "null" value and an actual value.  (You could say that my suggestion isn't "Year one" compatible.  <g>)  The trick (or hack) is to pick a number that won't ever be encountered in your actual data.

Robert Jacobson
Friday, January 23, 2004

This is an example of why no-one under the age of 40 (?) should be allowed to design a class library. There is a lack of commercial applications development experience evident in most libraries, here is one (rather common) instance.

In fact, given that ODBC has a perfectly usable and understandable interface to any relational database, your best option is to forget database class libraries completely, and get as close to the SQL as you possibly can.

HeWhoMustBeConfused
Friday, January 23, 2004

The problem with using the default DateTime value is that it doesn't store correctly in SQL Server. The value that DateTime structs have when you don't initialise them represents an invalid date time in SQL Server.

Whenever I stored them, I retrieved a different value. Eventually, I used a magic number against which I compared every DateTime value. If I retrieve a NULL from the DB, I set the DateTime to the magic value. If I'm storing a DateTime, I check it against the magic value -- equal and I store a NULL, otherwise store the value.

THIS IS SO INCREDIBLY STUPID.

But that's what you get for using .Net. And the SqlDateTime version isn't really nullable. The value can represent NULL; but the value can not BE null. As I recall, you can't write:

if (null==someStupidSqlDateTimeVariable)
{
  ...
}

Jeff Watkins
Friday, January 23, 2004

> The value can represent NULL; but the value can not BE null
Why do you want the value to "be" null?  I think you should just get used to using SqlDateTime if you're representing a nullable datetime in SQL Server.
"if (null==someStupidSqlDateTimeVariable)" will give you a compile error so you won't use it inadvertently.
What's so hard about using the correct syntax:
"if(someStupidSqlDateTimeVariable.IsNull)".

I really don't see how the VB Variant with its Nothing, Empty, Null values is better than this.

Joe
Saturday, January 24, 2004

The reason testing a DateTime value against NULL is important is that it allows you to treat DB values in a uniform manner. For example:

object v= <Use reflection to get the value out of the object>
if (NULL==v)
{
    <set parameter on the command to DBNull>
}
else
{
    <set parameter to v>
}

As it currently stands I need to do something like:

object v= <Use reflection...>
if (NULL==v ||
    (<v isa DateTime> && v==NullDateTime.Value)
{
    <set parameter to DBNull>
}
else
{
    <set parameter to v>
}

If SqlDateTime were at least a class rather than a struct that would help. Instead, .Net tries to solve the fundamental flaw of making DateTime a struct (and thereby non-nullable) by creating another struct that has a flag to indicate a null value.

That would be sort of like storing the text NULL in a string instead of simply storing a NULL value.

In general I find C# and .Net to be reasonably well thought out, if a little more complicated than necessary; but there seem to be some really fundamental parts that were designed or implemented by people who didn't "get it".

Jeff Watkins
Saturday, January 24, 2004

"The reason testing a DateTime value against NULL is important is that it allows you to treat DB values in a uniform manner."

Really? You have no null scalars in .NET. So how do you "uniformly" test for null ints, for example?

Brad Wilson (dotnetguy.techieswithcats.com)
Saturday, January 24, 2004

.Net doesn't have NULL-able scalars but it does have Integers objects which can be null. This makes all the difference.

Jeff Watkins
Tuesday, January 27, 2004

*  Recent Topics

*  Fog Creek Home