Re: Is there a column-data-type restricted to truncated date format?

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 12 Apr 2010 07:54:02 -0700 (PDT)
Message-ID: <3f8c168e-174a-4055-ace1-9101e06d824e_at_c36g2000yqm.googlegroups.com>

On Apr 12, 6:22 am, Norbert Winkler <norbert.winkl..._at_gmx.de> wrote:
> Hi,
>
> for creating a unique index with a date-column A_DATE I'm using a
> calculated column X_DATE as (trunc(A_DATE)):
>
> CREATE UNIQUE INDEX UX_A_TABLE
>   ON A_TABLE (
>     ...,
>     X_DATE
>   )
> /
>
> Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
> times a day.
> Is there a column-data-type restricted to truncated date format?
>
> --
> Norbert
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
> Production

Normally indexes on date columns are non-unique but if you want to restrict a user to only one entry per day then using trunc(sysdate) would result in a time of midnight. All date columns always have a time component. However I think you would need composite index on user and date if you want to perform this test per user.

Either a column constraint or perhaps a before insert trigger that truncates the input date value might suite your need.

HTH -- Mark D Powell -- Received on Mon Apr 12 2010 - 09:54:02 CDT