Databasen - NULL dates

Jess Robinson castaway at desert-island.demon.co.uk
Fri Oct 20 14:05:05 BST 2006



On Thu, 19 Oct 2006, Ovid wrote:

> --- Nigel Rantor <wiggly at wiggly.org> wrote:
>> We got into a discussion about whether or not to use NULL values for
>> dates that are as-yet-unknown. anyone have strong feelings and
>> arguments either way?
>
> If you're going to use NULL for an unknown date, you must be very aware
> of the pitfalls involved.
>
> As a trivial example, let's say that you have an orders table and an
> invoices table.  Your business rules state that invoice dates must
> never be the same as order date and you must never invoice someone on
> Christmas.
>
>  +-------------+
>  |    ORDERS   |
>  +----+--------+
>  | id |   date |
>  +----+--------+
>  |  1 | 010101 |
>  +----+--------+
>
>  +-------------+----------+
>  |         INVOICES       |
>  +----+--------+----------+
>  | id |   date | order_id |
>  +----+--------+----------+
>  |  7 |   NULL |        1 |
>  +----+--------+----------+
>
> So we want to select all invoices where the order date and the invoice
> date don't match and where the invoice date isn't Christmas (pretend
> this matches your SQL variant):
>
>  select o.id, i.id
>  from   orders o, invoices i
>  where  o.id = i.order_id
>    and  ( o.date <> i.date or i.date <> '25122007' );
>
[ snip explanation]

I'd call that a spurious example, the question was for "unknown dates", if 
you're creating a system of invoices, and you expect the dates to be 
unknown at some point, something is very wrong. I would expect anyone 
designing the thing to a) know when to set nullable, and when not, and b) 
set it on purpose, and thus also have it in mind when they're creating 
such queries..

Jess



More information about the london.pm mailing list