|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 2nd, 2004, 03:02 PM
|
|||
|
|||
|
triggering/DATE, month question
my code is below, what i want to do is to change the year automatically after july each year. this gives me the error: inconsistent datatypes, expected UDT got Number
Code:
create or replace trigger change_Year after update on student for each row Declare A student_type; B student_type; Begin IF (SYSDATE > '1-JUL') THEN select s.YEAR into A from student s; IF (A = 1) then B:=s.year+1; elsif (A = 2) then B:=s.year+2; else delete from student; end if; end if; End; /
|
|
#2
December 3rd, 2004, 10:45 AM
|
|||
|
|||
|
If you are working with fiscal years that end on June 30, you shoyuld be using Jun 30,
not Jul 01  This is how find out if you are past Jul 01 in a given year. Code:
set serverout on size 1000000
DECLARE
year VARCHAR2(5):=NULL;
mydate DATE:=NULL;
BEGIN
DBMS_OUTPUT.enable(100000);
year:=TO_CHAR(SYSDATE ,'YYYY');
mydate:=TO_DATE('01-JUL-' || year);
IF TO_NUMBER(TO_CHAR(mydate,'J')) > TO_NUMBER(TO_CHAR(SYSDATE,'J'))
THEN
DBMS_OUTPUT.put_line('Today is before or equal to July 01');
ELSE
DBMS_OUTPUT.put_line('Today is after July 01');
END IF;
END;
/
You can also use the greatest() function to compare dates.
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > triggering/DATE, month question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
