Re: New Check Constraint Condition Question...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 12 Sep 1999 09:04:36 -0400
Message-ID: <4aPbN585N2Qpcnnr8udhgatxL=+d@4ax.com>

A copy of this was sent to Stephen Hurrell <hurrells_at_baynet.net> (if that email address didn't require changing) On Sat, 11 Sep 1999 13:42:00 -0400, you wrote:

>>
>
>Hello
>
>First off I'd like to thanks everyone who respond to my first constraint
>question.
>The solution that I chose was;
>
> event_year = to_char(event_date,'YYYY')
>
>However the trigger approach may become applicable.
>
>Anyways I now what to know if you can use sysdate in a calculation.
>
>I have two fields "age" and "birth_date" that are related as;
>
> age = yearof(sysdate)-yearof(birth_date)
>
>QUestions;
>
>1. Can I use sysdate here or like this?
>2. There are Y2K issues with the above
>

You can use sysdate in calculations.

You could use the months between to compute the age of something in years.

tkyte_at_8.0> declare
  2 p_birthdate date default '01-Jan-1900';   3 begin

  4          dbms_output.put_line( 'You are are ' || 
  5                trunc( months_between( sysdate, p_birthdate )/12) );

  6 end;
  7 /
You are are 99

PL/SQL procedure successfully completed.

>Any ideas?

Make sure to NOT store the age in years in the database anywhere -- use a view to make a virtual column "age" if you need to. You can use months_between and trunc in SQL on a database column as well as in plsql as above.

>STeve
>----EOT
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Sep 12 1999 - 08:04:36 CDT