Re: Does "0" exist for DATE?

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 Mon Jan 10 2000 - 14:57:03 CST