Re: Does "0" exist for DATE?

Something to watch out for when using 'special' values instead of NULL's is interfaces to other systems. While it may make sense within the context of a particular application or database, the special value may be wildly misinterpreted if it is sent to another system ( a birth date of 01/01/0001 springs to mind). With the increase in inter-system communication via such mechanisms as XML, I am becoming increasingly wary of using constructs like special values in my database design.

Regards, David.

Thomas Kyte wrote:

> A copy of this was sent to Brad <Brad_at_SeeSigIfThere.com>
> (if that email address didn't require changing)
> On Mon, 10 Jan 2000 14:01:42 -0500, you wrote:
>
> >In article <54qb7s8o8loguooj4csfsb005sn3nem9jm_at_4ax.com>,
> >tkyte_at_us.oracle.com says...
> >> A copy of this was sent to Casey Claiborne <mscasey_at_io.com>
> >> (if that email address didn't require changing)
> >> On Fri, 7 Jan 2000 06:35:20 -0600, you wrote:
> >>
> >> >
> >> >Hello,
> >> >
> >> >Is there a way to initialize DATE with a default? For example, an
> >> >INTEGER type can be set to 0, so can a FLOAT.
> >> >
> >> >For example, suppose I have a field for a Customer called
> >> >DateOfMarriage. If the customer is not married, this field does not
> >> >apply. What can I set it to in the database? If I use NULL, then
> >> >I will have to worry about ISNULL or ISNOTNULL situations.
> >> >
> >> >Any help on this issue would be *greatly* appreciated :)
> >>
> >> just as you did for the int and float, pick some ARBITRARY value to put the in
> >> the field (ZERO is some arbitrary number you have decided means "null" in your
> >> sense -- to others, the presence of a ZERO means something).
> >>
> >> I've used a date such as "to_date( '01010001', 'mmddyyyy' )" myself in the past
> >> -- january 1'st, year 1. You just need to pick a date that cannot in any way
> >> appear in your data normally...
> >
> >That's what null is for. In this case a null should work fine because if
> >you are restricting on the field a record with a null should not show up
> >in the result.
>
> I've used it when I needed the date field be be single-ly indexed (not part of a
> concatenated index) and had few 'NULL' dates (so the index is not out of wack...
> Since singleton nulls are not indexed in B*Trees -- using a NULL was not an
> option in this case.
>
> Some people hate nulls -- I use them sometimes, sometimes not. Depends on your
> needs.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed Jan 12 2000 - 18:42:04 CST