Re: Composite Key Using Year from a Date

From: S. Anthony Sequeira <nobody_at_127.0.0.1>
Date: Fri, 22 Apr 2011 20:07:12 +0100
Message-ID: <4db1d1e0$0$12160$fa0fcedb_at_news.zen.co.uk>

On 22/04/11 18:21, jimmyb wrote:
> Users canupdate the record as often as they need - within the calendar
> year. Once a calendar year changes they should not be making any
> updates. The application has a business rule only to display
> competencies for the current year.
>
> I have another problem with this solution though. I'm an error when I
> insert a record.
>
> create table user_competencies
> ( user_id number
> ,comp_id number
> ,user_score varchar2(1)
> ,supervisor_score varchar2(1)
> ,update_dt date
> ,update_yr date generated always as (TO_DATE(update_dt,'YYYY'))
> )
> /
>
> alter table user_competencies add constraint user_comp_pk primary
> key(user_id,comp_id,update_yr);
>
> insert into user_competencies
> (user_id, comp_id, user_score, supervisor_score, update_dt)
> values( 10
> , 1
> , 'a'
> , 'a'
> , sysdate-1
> ) ;
>
> ERROR at line 1:
> ORA-01830: date format picture ends before converting entire input
> string

Try

select to_date( sysdate,'yyyy') from dual;

and then try

select trunc(sysdate,'yyyy') from dual;

-- 
Tony Sequeira
++

Received on Fri Apr 22 2011 - 14:07:12 CDT