Re: Just want day but no time information

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1076691285.413874_at_yasure>...
> D. Alvarado wrote:
>
> > Hello, I'm running Oracle 8.1.7 for Solaris. I want to impose a
> > constraint on one of my tables that no two dates can be stored that
> > are from the same day. That is, if a row contains the date 2-13-04
> > 7:56 AM, I don't want anyone to be able to create a row with the date
> > 2-13-04 11:26 AM. Ideally, it would be great if there's a column type
> > that just has day (but no time) information, but I can't seem to find
> > such a column type. Any advice on how I can make this constraint
> > work?
> >
> > Thanks - Dave
>
> Store information as TRUNC(<date>) and place a unique constraint on
> the column.

If only the day is needed in the data and not the time, then Daniel's way is the best. If you only store the day information using TRUNC, then you won't have to worry about showing only day info in various selects etc.

But if your requirement is to only allow one row per day value, but to remember the time as well - then you can get such a constraint using a function based index :

create unique index index_name on table_name (TRUNC(date_column_name));

That will give you a unique constraint on the day portion of the date, but allow you to store both the day and the time.

/KiBeHa Received on Mon Feb 16 2004 - 05:53:32 CST