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