Re: Date Types

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/03/14
Message-ID: <38d5a283.19559074@netnews.worldnet.att.net>#1/1

It is true that the date type includes a time component. It can store date and time down to the second. To use a date in your primary key, and ensure that no one ever stores a time, you can use a trigger. For example:

SQL> describe x

 Name                                Null?    Type
 ------------------------- -------- -----
 Y                                              DATE

create or replace trigger date_only
before insert or update on x
for each row
begin

   :new.y := trunc(:new.y);
end;
/

You might be able to fine-tune this using a WHEN clause, or and perhaps by breaking it up into two separate triggers, so that it doesn't execute when you UPDATE columns other than the date field.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Mon, 13 Mar 2000 20:42:38 -0000, "Markus Janscha" <markus_at_janscha.co.uk> wrote:

>I would like to set the input date as the primary key ae: each day only
>allows one entry .
>What data type do I use? I read in the Oracle manual that the date type also
>stores hours.
>I need to store the day (dd-mm-yyyy) only and make this unique. I should
>also still be able to do date arithmetic.
Received on Tue Mar 14 2000 - 00:00:00 CST