Board index » delphi » DateTime and Null

DateTime and Null

Is there a clean way to handle a datetime entry with a date picker that will
handle nulls?

It seems that all of the components don't deal with this very well at all
and you end up with an arbitrary date (Dec 30/1899)stored in your SQL table.
I'm told this is because the underlying datatype of TDateTime doesn't
support Null and so the controls can't either.

I can write a trigger to check the value in an entry field and if it equals
Dec 30/1899 to put a null in its place but that seems like uneccesary work
as the component should be able to deal with the situation.

How are people dealing with this (or are they)?

Richard

 

Re:DateTime and Null


Richard,

The db-data entry components don't handle null values very well
because a null value can have many different meanings.  The actual
meaning of a null value depends upon the semantics of the
application.

In your date field, does null mean "not applicable", "unknown", "not
assigned yet", or even "eternity + 1"?

There's no way for a db-data entry component to figure out what it
should display when encountering a null value.  The developer has to
do that.  For example, if null means "not applicable" in the context
of the rest of the data in the row, the data entry field should be
disabled.  If it means "not assigned yet", then the data entry field
should be enabled so the user can enter a value.  How can the control
know this?

For my part, I avoid nulls like the plague.  They cause all kinds of
semantic problems (like the ones mentioned above, plus many others).  
Their treatment by the various SQL vendors is another headache.  
Quick, what is NULL divided by zero?  It depends which SQL server you
ask.  On some systems it's NULL, and on others it's an error.  Nulls
also propagate in expressions.  "1 + 2 + null" always equals null.

The non-null solution I use for a date column is to add a boolean
column which gives meaning to the date column.

For example, let's say I have a date column WARRANTY_EXPIRATION in
the PRODUCTS table.  Of the many products I sell, some of them have
lifetime warranties.   Should I let a null value in the
WARRANTY_EXPIRATION column indicate a lifetime warranty?  I would
have to write application logic for the data entry form which
prevented the user from entering a date in this field if it was null.    
I would also have to make sure this was thoroughly documented so a
maintenance programmer would not accidentally "improve" this code.

On the other hand, if - instead of using nulls - I added a boolean
column called LIFETIME_WARRANTY to the PRODUCTS table, the logic of
the situation becomes much clearer and self-documenting.  I've
replaced an ambiguous (null) situation with a clearly understandable
one (boolean).

If you want an excellent in-depth discussion of null values in SQL,
get a copy of "SQL for Smarties" by Joe Celko.

HTH,

Chris.
---------------

Re:DateTime and Null


To me "unknown" can be treated the same as "not entered yet", but I guess
that is subjective, right<g>?

On the whole I like to avoid NULLs as well but I also don't like having a
"real" date show up in my database when I didn't enter it. There may not be
a perfect solution but I think leaving a date entry blank and storing a null
would work for the vast majority of developers.

Just because a solution doesn't solve all potential issues is no reason to
not do it at all. And considering how often dates are used in applications I
think doing nothing at all and leaving the programmer to deal with every
situation by hand-coding is a bad way to handle it. There could be two date
controls descended from a common control (or just a property) that states
how to handle Null (Disable Control/Enable Control).

I think the problem stems from the fact that Delphi (and Object Pascal) were
probably originally geared to work with Paradox tables which I don't think
had the concept of Null (I could be wrong).  So the DateTime was implemented
as a Double which doesn't understand Null either.  I think we need a new
type that understands Null.

Anyway, it doesn't look like there is a simple solution to this so I better
get back and make this thing work. I just don't like the idea of the extra
column everywhere to tell me how to deal with the original column. You now
have more work to do in each instance of it which means more potential for
missing one and causing errors.

Thanks for your thoughts.

Richard

PS. Formally I believe any operation with a NULL results in a NULL (or at
least it should<g>)

"Chris R. Timmons" <ctimm...@NOSPAMlgrs.com> wrote in message
news:Xns90D9BA9F8EA07ctimmonslgrscom@207.105.83.65...

Quote
> Richard,

> The db-data entry components don't handle null values very well
> because a null value can have many different meanings.  The actual
> meaning of a null value depends upon the semantics of the
> application.

> In your date field, does null mean "not applicable", "unknown", "not
> assigned yet", or even "eternity + 1"?

> There's no way for a db-data entry component to figure out what it
> should display when encountering a null value.  The developer has to
> do that.  For example, if null means "not applicable" in the context
> of the rest of the data in the row, the data entry field should be
> disabled.  If it means "not assigned yet", then the data entry field
> should be enabled so the user can enter a value.  How can the control
> know this?

> For my part, I avoid nulls like the plague.  They cause all kinds of
> semantic problems (like the ones mentioned above, plus many others).
> Their treatment by the various SQL vendors is another headache.
> Quick, what is NULL divided by zero?  It depends which SQL server you
> ask.  On some systems it's NULL, and on others it's an error.  Nulls
> also propagate in expressions.  "1 + 2 + null" always equals null.

> The non-null solution I use for a date column is to add a boolean
> column which gives meaning to the date column.

> For example, let's say I have a date column WARRANTY_EXPIRATION in
> the PRODUCTS table.  Of the many products I sell, some of them have
> lifetime warranties.   Should I let a null value in the
> WARRANTY_EXPIRATION column indicate a lifetime warranty?  I would
> have to write application logic for the data entry form which
> prevented the user from entering a date in this field if it was null.
> I would also have to make sure this was thoroughly documented so a
> maintenance programmer would not accidentally "improve" this code.

> On the other hand, if - instead of using nulls - I added a boolean
> column called LIFETIME_WARRANTY to the PRODUCTS table, the logic of
> the situation becomes much clearer and self-documenting.  I've
> replaced an ambiguous (null) situation with a clearly understandable
> one (boolean).

> If you want an excellent in-depth discussion of null values in SQL,
> get a copy of "SQL for Smarties" by Joe Celko.

> HTH,

> Chris.
> ---------------

Re:DateTime and Null


Quote
In article <3b4b1dc8$1_1@dnews>, Richard wrote:
> To me "unknown" can be treated the same as "not entered yet", but I guess
> that is subjective, right<g>?

Actually these are two distinct cases.

Mike Orriss (TeamB and DevExpress)

Re:DateTime and Null


I realize that they are in fact two distinct cases but I think for most
developers the Null would be treated as Not Entered Yet.  There may be cases
where you need it to looked at as Unknown but I think in most business
applications the Unknown option would be far rarer than Not Entered Yet.

And I wasn't asking that it always be treated the way I want to see it.  I
would like to see it have the ability to work my way without my having to do
extra coding all of the time.  And I suspect a lot of coders are having to
code around this so why not make life a little easier for them?

Cheers

Richard

"Mike Orriss (TeamB)" <m...@3kcc.co.uk> wrote in message
news:VA.000023a4.1772ff69@mikemain...

Quote
> In article <3b4b1dc8$1_1@dnews>, Richard wrote:
> > To me "unknown" can be treated the same as "not entered yet", but I
guess
> > that is subjective, right<g>?

> Actually these are two distinct cases.

> Mike Orriss (TeamB and DevExpress)

Re:DateTime and Null


Richard,

Quote
> I realize that they are in fact two distinct cases but I think
> for most developers the Null would be treated as Not Entered Yet.

Actually there are at least four{*word*249} different cases, and probably
many more (see "The Interim Report 75-02-08 to the ANSI X3 (SPARC
Study Group, 1975)").  A null takes the place of any "non-data", such
as unknown, overflows, underflows, errors, not-applicable, not-
entered-yet, infinity, eternity, not-a-number, etc, etc, etc...

Quote
> There may be cases where you need it to looked at as Unknown but
> I think in most business applications the Unknown option would be
> far rarer than Not Entered Yet.

And I think the opposite.  And another programmer might have a
completely different view.  But that doesn't make one of us right and  
the other wrong.  Right and wrong don't enter into this argument.

Nulls are simply ambiguous - they don't convey any meaning.  The
programmer has to give them meaning.  There is no way around this.  
IMHO, using some kind of default assumption as to what null means
would be an error-prone waste of time.

Quote
> I would like to see it have the ability to work my way
> without my having to do extra coding all of the time.

A future version of the ANSI SQL standard might support something
like this, but that capability doesn't exist right now.  And I don't
think you're doing any "extra" coding - you're just writing the code
which is necessary to do the job given the current state-of-the-art.

Just my $0.02.

Chris.
---------------

Re:DateTime and Null


"Chris R. Timmons" <ctimm...@NOSPAMlgrs.com> wrote in message
news:Xns90DBC566ADB94ctimmonslgrscom@207.105.83.65...

Quote
> Richard,

> > I realize that they are in fact two distinct cases but I think
> > for most developers the Null would be treated as Not Entered Yet.

> Actually there are at least four{*word*249} different cases, and probably
> many more (see "The Interim Report 75-02-08 to the ANSI X3 (SPARC
> Study Group, 1975)").  A null takes the place of any "non-data", such
> as unknown, overflows, underflows, errors, not-applicable, not-
> entered-yet, infinity, eternity, not-a-number, etc, etc, etc...

> > There may be cases where you need it to looked at as Unknown but
> > I think in most business applications the Unknown option would be
> > far rarer than Not Entered Yet.

> And I think the opposite.  And another programmer might have a
> completely different view.  But that doesn't make one of us right and
> the other wrong.  Right and wrong don't enter into this argument.

> Nulls are simply ambiguous - they don't convey any meaning.  The
> programmer has to give them meaning.  There is no way around this.
> IMHO, using some kind of default assumption as to what null means
> would be an error-prone waste of time.

> > I would like to see it have the ability to work my way
> > without my having to do extra coding all of the time.

> A future version of the ANSI SQL standard might support something
> like this, but that capability doesn't exist right now.  And I don't
> think you're doing any "extra" coding - you're just writing the code
> which is necessary to do the job given the current state-of-the-art.

> Just my $0.02.

> Chris.
> ---------------

Unfortunately, the existing Delphi components (actually, the data
structures) attempt to resolve this ambiguity by defaulting to 0 (= Jan 30,
1899).  It would be much better if the data types and components were
intelligent enough to leave the data as ambiguous, and leave it up to the
developer to put meaning to it.  Assigning an arbitrary value doesn't
alleviate the developer's need to deal with it.  In fact, if Jan 30, 1899
can be a valid value (date of a museum artifact, birthdate of a geriatric
patient), then you have a real hassle on your hands.  If Delphi needs to
resolve this, it'd be better if it converted nulls to Jan 1, 0001, or
something similarily unlikely to be used.

The problem also appears for numeric values, like where $0.00 is a valid
cost.  The only way I can see around it is to use Variants for all data
fields that can be null.  Too bad they're So Slow!

Daniel Bragg

Re:DateTime and Null


On Tue, 10 Jul 2001 09:26:04 -0600, "Richard" <rtsmat...@hotmail.com>
wrote:

Quote
>To me "unknown" can be treated the same as "not entered yet", but I guess
>that is subjective, right<g>?

Null, at least as far as I know, is treated as undefined within SQL
and DB's... it has no meaning, except that which you give it... in
code later.

I believe SQL defines maths logic to always compute to Null.

I'm not sure about string manipulation, I believe concatination works
as though Null is a 0 length string?, but sorting can be problimatic
dependent on DB implimentation, and by inferrence MIN(x)/MAX(x). But I
believe that Null in theory has a lower value than '<blank>' and 0.

I agree with another poster that a flag (single character) aught to be
used to indicate a fields meaning where it may be ambiguois (ie
NeverExpires, UseExpiryDate, ExpiryUnknown).

However as an asside, I find the arbitary nature of lower limit too
dates in SQL server to be very annoying (I believe delphi also has
this limitation in its date processing?) especially if one wants to
store data that is hystorical (such as building construction dates)
1899 is only 100 years old! The only way arround this is to not use
date types which is fine untill you need to do date maths, which
introduces additional [visible] code :-(

Jon

--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

Other Threads