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

>> 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?

Err, doesn't that depend on the index definition? I don't see any general rule that would make indexes specifically on DATE columns non unique. Or did you mean that usually people define non unique indexes on DATE columns?

> 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.

What user? Am I missing something from the original question?

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

I assume, with the constraint you would ensure that each DATE inserted would be a truncated one.

Cheers

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/